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
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.
