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.
Example Script
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.
Reference
- About DATEPART at Microsoft Docs.
Hi. Your code is going to save me a bunch of time after some tweaks but i am having one issue trying to figure out how to make the start of the week a Monday and not a Sunday and i am not seeing where that is set in the SQL Script. I have tried changing some some values and none of them seem to give me what i need. Any thoughts would be appreciated. Thanks,Matt
select DATEADD(wk,DATEDIFF(wk,0,getdate()),0)