Calendar Table - Week number of month
update TCalendar
set = WeekNumberOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, FullDateAlternateKey), 0), FullDateAlternateKey) +1
Power BI - Convert Week Number to Month of the year
Your question is not entirely clear.
Assuming you want to get the
- Month Name from the date of the first day of the week given by the week number
- and the week number is computed as an ISO week number,
Then I would first add a Custom Function to compute the relevant Date:
//fnDateFromISOwn
//compute date of the first day of the week given an ISO weeknumber
//if the Year is not entered, it will default to "this" year
//Year must be a full year: eg 2021 vs 21. The latter will not be interpreted as the year 2021.
(wn as number, optional year as number) =>
let
yr = if year = null then Date.Year(DateTime.LocalNow()) else year,
wn1Start = Date.StartOfWeek(#date(yr,1,1),Day.Monday),
w1 = if Date.AddDays(wn1Start,3) < #date(yr,1,1) then Date.AddDays(wn1Start,7) else wn1Start
in Date.AddDays(w1, 7*(wn-1))
Then you can add a custom column in your main code:
#"Added Custom" = Table.AddColumn(#"Previous Step", "Month", each
Date.MonthName(fnDateFromISOwn([Week Number], Date.Year([New Status Date]))))
Source
Results
On the other hand,
- if there is a relationship in that the weeknumber always corresponds to the
New Status Date
, - and it is still an ISO weeknumber,
- and you still want the month name corresponding to the date at the start of that week
then you can use the simpler formula where you merely compute the previous Monday to the New Status Date
, and obtain the MonthName from that date.
Date.MonthName(Date.StartOfWeek([New Status Date],Day.Monday)))
How to get the Week of the month (1:6)
The DatePart function is perfect for this. Using DatePart
with the interval set to "weeks" you can find the week of a given date. Then you subtract the number of weeks before the first day of that month (which sets the first day to week = 1).
Function WeekNumOfDate(D As Date) As Integer
WeekNumOfDate = DatePart("ww", D) - DatePart("ww", DateSerial(Year(D), Month(D), 1)) + 1
End Function
Here is a second version of the function that has the ability to set the FirstDayOfWeek
argument:
Function WeekNumOfDate(D As Date, Optional FirstDayOfWeek As VbDayOfWeek = vbSunday) As Integer
WeekNumOfDate = DatePart("ww", D, FirstDayOfWeek) - DatePart("ww", DateSerial(Year(D), Month(D), 1), FirstDayOfWeek) + 1
End Function
As an example for using FirstDayOfWeek
: With FirstDayOfWeek
set to vbThursday
, the date "Nov 5th, 2021" will return as Week 2, whereas it would by default be counted as Week 1. November 1st to 3rd of 2021 will be week 1, and then 4th to 10th will be week 2.
Week number of the month?
In order to use straight division, the day of month for the date you're looking at needs to be adjusted according to the position (within the week) of the first day of the month. So, if your month happens to start on a Monday (the first day of the week), you can just do division as suggested above. However, if the month starts on a Wednesday, you'll want to add 2 and then do the division. This is all encapsulated in the function below.
from math import ceil
def week_of_month(dt):
""" Returns the week of the month for the specified date.
"""
first_day = dt.replace(day=1)
dom = dt.day
adjusted_dom = dom + first_day.weekday()
return int(ceil(adjusted_dom/7.0))
MS SQL week number of month at date
September 30, 2019 is in the 5th week, not the sixth, if the default first day of the week is used. That is specified by the DATEFIRST setting. This affects date calculations like DATEADD
and DATEPART(weekday)
If you want to use a different first day, eg Monday, you'll have to change the setting with
SET DATEFIRST 1;
When you make that change, your last query works both for 2019-09-01
and 2019-09-30
set datefirst 1
declare @date datetime = '2019-09-30'
select (day(@date)+datepart(weekday,dateadd(day,1-day(@date),@date))- datepart(weekday,@date))/7+1
This returns 6. For 2019-01-01
it returns 1
Fetch week numbers with date range of each week for a whole year
There are a lot of ways to solve this problem. A brute force approach generates all the dates using a some method -- this uses a recursive CTE -- and then assigns the weeks based on a window function counting the Mondays.
with cte as (
select datefromparts(2021, 1, 1) as dte
union all
select dateadd(day, 1, dte)
from cte
where dte < datefromparts(2021, 12, 31)
)
select weeknum, min(dte), max(dte)
from (select cte.*,
sum(case when datename(weekday, dte) = 'Monday' then 1 else 0 end) over (order by dte) as weeknum
from cte
) cte
group by weeknum
option (maxrecursion 0);
This is fine for a one-off query, but isn't the most efficient method to generate the weeks. It is, however, a fun illustration of recursive CTEs. That said, you should probably really have a calendar table of some sort that encapsulates this information as a table.
Related Topics
Why (And How) to Split Column Using Master..Spt_Values
How to Send Email from Postgresql Trigger
Replacing Null with 0 in a SQL Server Query
Calculate Business Days in Oracle SQL(No Functions or Procedure)
How to Insert Values into a Table, Using a Subquery with More Than One Result
Join Comma Delimited Data Column
How to Select a Substring in Oracle SQL Up to a Specific Character
"In" Clause Limitation in SQL Server
SQL Server Loop - How to Loop Through a Set of Records
Preventing Adjacent/Overlapping Entries with Exclude in Postgresql
Find Closest Numeric Value in Database
How to Return Rows with a Specific Value First
Oracle -- Split Multiple Comma Separated Values in Oracle Table to Multiple Rows
MySQL - Selecting Data from Multiple Tables All with Same Structure But Different Data
Scope of Temporary Tables in SQL Server
How to Find Column Names for All Tables in All Databases in SQL Server