T-SQL : Get Weekday Number of the Month
Introduction
This article is an outcome of my answer to a question on MSDN forum - to return the weekday number for the month corresponding to a passed date value.
For example, if the passed date value is 7 Apr 2014, then value returned should be 1 as it is the first Monday of the month. Similarly 14 Apr 2014 will return 2 as it is the 2nd Monday. Similarly 18th Apr 2014 will return 3 as it is 3rd Friday. I gave the solution as below:
-- date variable to pass as your value
DECLARE @dt datetime
SET @dt='20140310'--pass any date here
-- solution
SELECT (DATEPART(wk,@dt)-DATEPART(wk,DATEADD(mm,DATEDIFF(mm,0,@dt),0))) + CASE WHEN DATEDIFF(dd,0,@dt)%7 >= DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm,0,@dt),0))%7 OR DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm,0,@dt),0))%7 = 6 THEN 1 ELSE 0 END AS weekdaynumberformonth
Code description:
DATEDIFF using the week argument will give number of weeks elapsed between start of the month till the passed date. Then we need to compare the weekday value of month start date and passed the date value to see if weekday value of our date happens after the month start date (i.e., say, our passed date is a Thursday whereas month start is Wednesday etc). If passed day is later than or equal to month start date we will add one more to the week count otherwise week count itself will give you weekday number of the day for the month.
Please keep in mind the logic used above uses a method to determine an absolute weekday value for each dates rather than depending on DATEFIRST setting of the server. More information on this can be seen in this link.
The above shown code can be converted into a UDF as shown below:
CREATE FUNCTION dbo.GetWeekDayNumberOfMonth
(
@dt datetime
)
RETURNS int
AS
BEGIN
DECLARE @WeekDayNumberOfMonth int
SELECT @WeekDayNumberOfMonth= (DATEPART(wk,@dt)-DATEPART(wk,DATEADD(mm,DATEDIFF(mm,0,@dt),0))) + CASE WHEN DATEDIFF(dd,0,@dt)%7 >= DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm,0,@dt),0))%7 OR DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm,0,@dt),0))%7 = 6 THEN 1 ELSE 0 END
RETURN (@WeekDayNumberOfMonth)
END
To execute the above created UDF, run the below code:
SELECT dbo.GetWeekDayNumberOfMonth('20131220')