In my previous sql tip, we have seen how to get the week number of year. This time, we will see how to get the week umber of month. Finding week number in a month is not a straightforward way. There is no inbuilt function to get this. We have to create our own script using several date functions to get it. Here is one of the method to find the monthly week number.
In this script, I have used DATEADD function along with EOMONTH function to get the first day of the month for the given date. Then used DATEPART with week parameter to get the yearly week number for the given date and the first day of month. Finally subtracted the yearly week number of first day of the month from yearly week number of given date and added 1 to get the monthly week number of the given date.
DECLARE @date_given datetime = '2019-06-02'; SELECT (DATEPART(week, @date_given) - DATEPART(week, DATEADD(day, 1, EOMONTH(@date_given, -1)))) + 1; GO /* Result */ 2
NOTE: I have considered first day of the week as Sunday. Partial week is also considered as 1 week. For example, June 2019 starts on a Saturday. So Saturday 2019/06/01 is week 1 and Sunday 2019/06/02 is week 2.
- About DATEPART at Microsoft Docs.