Tuesday, June 4, 2013

Get the latest weekday of month in SQL 2012

Recently there was a requirement to get latest weekday of a month.
I made use of a solution based on the EOMONTH function available in SQL 2012 for that, which I'm sharing through this blog.
The UDF will look like below

CREATE FUNCTION GetLastWeekDayOfMonth
(
@Weekday int,--value corresponding to weekday ( 0- monday,1 - Tuesday ,... 6- Sunday)
@Year int,-- year for which we need to get date
@Month int -- month of the year
)
RETURNS datetime 
AS
BEGIN
DECLARE @date datetime = EOMONTH(DATEADD(yyyy,@year-1900,0),@Month-1)

SELECT @date=CASE WHEN DATEDIFF(dd,0,@date) % 7 < @WeekDay THEN @date -  (7-(@WeekDay-DATEDIFF(dd,0,@date) % 7)) ELSE @date - (DATEDIFF(dd,0,@date) % 7-@WeekDay) END
RETURN (@date)
END

The function can be invoked as below

SELECT dbo.GetLastWeekDayOfMonth(4,2013,2)

This will retrieve the latest Friday (ie day corresponding to weekday value of 4) for the 2nd month of 2013. Now lets see the result.

output
----------------------
2013-02-22 00:00:00.000

If we check we can see that this is the last  Friday of February 2013
Pass any value for weekday for month year combination and you'll get corresponding last weekday value returned.
Hope this will be useful for someone for similar requirement. Feel free to let me know what you think of this.