How to find last weekday of current month using SQL

Question

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
set @date='1/4/13'
select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))

Answer 1

select case when datename(dw,(EOMONTH(getdate()))) ='Saturday' then day(EOMONTH(getdate())) -1
        when datename(dw,(EOMONTH(getdate()))) ='Sunday' then day(EOMONTH(getdate())) -2
        else day(EOMONTH(getdate())) end

Answer 2

declare @date datetime ='1/4/19'
select datename(dw, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0)))

This will give you the day name of the last day of the month.

Answer 3

Quite a neat solution:

declare @date1 as date = '20130401'
declare @date as date= eomonth(@date1,0)
set @date = (select case
    when datepart(dw,@date) = 1 then dateadd(day,-2,@date) --when day is a sunday subtract 2 day to friday
    when datepart(dw,@date) = 7 then dateadd(day,-1,@date) --when day is a saturday subtract 1 day to friday
    else @date END)

Answer 4

I know that this is old, but I was looking for a one-liner and landed here. Here's the one I figured out:

set DATEFIRST 1

declare @testDate datetime
select @testDate = '8/1/2016'

select dateadd(day, -(select max(dayCount) from (values (DATEPART(WEEKDAY, EOMONTH(@testDate)) - 5), (0 )) as allDays(dayCount)), EOMONTH(@testDate))

This will only work with SQL Server 2012 and above.

How it works:

1. Get the last calendar date for the test date
2. Get the day-of-the week where Monday is 1, Saturday is 6 and Sunday is 7
3. Subtract the day-of-the week and then set it to 0 if it is negative, practically a max(0, day-of-the week - 5). This will leave 1 for Saturday or 2 for Sunday.
4. Subtract the days (1 if Saturday, 2 if Sunday) from the last day of the month

Answer 5

This one is much harder than it ought to be. I’ve done similar work using datename, but only because I know all the systems I use will be configured with English as the default language. Without that assumption, you have to use datepart(dw, ..., and you also have to worry about SET DATEFIRST. I am also assuming “weekday” means “Mon, Tue, Wed, Thu, Fri”, and excluding Saturday and Sunday. (Or does TFIG apply across the globe?)

Thus, walking through my methodology, we start with what you had:

DECLARE
  @Date datetime
 ,@BOM  datetime

--SET @Date = '1/4/14'  --  Should return Jan 31, 2013
SET @Date = '5/4/14'  --  Should return May 30, 2014 (not May 31)
SET @BOM  =  DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0)

PRINT @BOM

This sets @BOM (beginning of month) to the first day of the month after whatever you have in @Date. (I split this across multiple statements, because otherwise you have to repeat the function all over the place in the code below.)

Next, you have to “move back” from this day by one, two, or three days. If BOM is Monday, -3 to get to the prior Friday; if BOM is Sunday, -2 to get to Friday; otherwise, -1 will land you on a weekday. Based on the values returned by datepart, there’s no wifty algorithm I can think of to generate that -1/-2/-3 spread, so I use a case statement (I reject looping routines out of hand—far too kludgy for database work).

PRINT datepart(dw, @BOM)  --  To see what is is

PRINT dateadd(dd, case
                    when datepart(dw, @BOM) = 2 then -3
                    when datepart(dw, @BOM) = 1 then -2
                    else -1
                   end
                  ,@BOM)

Alas, this only works if your SQL instance is configured with the default “first day of week” setting; this is checked via PRINT @@datefirst, where 7 (the default) = Sun, 6 = Sat, and so forth. Once again, no wifty algorithm suggests itself, and the case statement turns into a mess:

PRINT dateadd(dd, case
                    when @@datefirst = 7 and datepart(dw, @BOM) = 2 then -3
                    when @@datefirst = 7 and datepart(dw, @BOM) = 1 then -2
                    when @@datefirst = 6 and datepart(dw, @BOM) = 3 then -3
                    when @@datefirst = 6 and datepart(dw, @BOM) = 2 then -2
                    when @@datefirst = 5 and datepart(dw, @BOM) = 4 then -3
                    when @@datefirst = 5 and datepart(dw, @BOM) = 3 then -2
                    when @@datefirst = 4 and datepart(dw, @BOM) = 5 then -3
                    when @@datefirst = 4 and datepart(dw, @BOM) = 4 then -2
                    when @@datefirst = 3 and datepart(dw, @BOM) = 6 then -3
                    when @@datefirst = 3 and datepart(dw, @BOM) = 5 then -2
                    when @@datefirst = 2 and datepart(dw, @BOM) = 7 then -3
                    when @@datefirst = 2 and datepart(dw, @BOM) = 6 then -2
                    when @@datefirst = 1 and datepart(dw, @BOM) = 1 then -3
                    when @@datefirst = 1 and datepart(dw, @BOM) = 7 then -2
                    else -1
                   end
                  ,@BOM)

Ugly, or what? And looping structures have to account for this as well. Of course, if you can rely on always having the same language on your SQL Instances, it’s that much simpler:

PRINT dateadd(dd, case
                    when datename(dw, @BOM) = 'Monday' then -3
                    when datename(dw, @BOM) = 'Sunday' then -2
                    else -1
                   end
                  ,@BOM)

Any or all of the above can and should be “concatenated down” into a single statement or query (or, better, a function); if you can safely make assumptions about your installation’s language and/or first day of week, you can shorten it even more.

Answer 6

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;

Answer 7

you can try using the week day function like

select datename(dw, getdate())

and then from there use that in your query to derive the last week day in the month

so you could end up with something like this without actually having to create a "dates" table

    declare @date datetime
set @date='3/4/13'
select case when datename(dw, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))) = 'Saturday'
            then DATEADD(d, -2, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))
            when datename(dw, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))) = 'Sunday'
            then DATEADD(d, -3, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))
            else DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))
            end
, case when datename(dw, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))) = 'Saturday'
            then datename(dw, DATEADD(d, -2, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0)))
            when datename(dw, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))) = 'Sunday'
            then datename(dw, DATEADD(d, -3, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0)))
            else datename(dw, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0)))
            end