How would I calculate the last weekday of the current month given a date using SQL?
I was able to get the last day of current month, but not sure how to do the last weekday programmatically.
I don't want to generate a calendar look-up table.
Here's the last day of month code i'm currently using:
declare @date datetime
select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))
I know it is not the most intuitive or effective or easy way of doing it. But here is my solution to find the last Weekday of the month...
declare @date datetime, @lastDate datetime, @lastWeekDay datetime set @date='05/4/2014';--'1/1/2014' set @lastDate = (SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))); /* @dayOfWeek represents -- 0-Monday through 7-Sunday */ declare @dayOfWeek INT = (SELECT DATEDIFF(dd, 0, @lastDate) % 7); /* If last date is sat/sun substract 1 or 2 days from last date */ set @lastWeekDay = (SELECT CASE WHEN @dayOfWeek = 5 THEN DATEADD(dd, -1, @lastDate) WHEN @dayOfWeek = 6 THEN DATEADD(dd, -2, @lastDate) ELSE @lastDate END) SELECT @lastWeekDay;