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

score 6 · Answer 1 · answered Nov 02 '18 at 16:27

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)

score 3 · Answer 2 · answered Nov 30 '16 at 17:55

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:

Get the last calendar date for the test date
Get the day-of-the week where Monday is 1, Saturday is 6 and Sunday is 7
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.
Subtract the days (1 if Saturday, 2 if Sunday) from the last day of the month

this looks pretty close. i think if you incorporate @@DATEFIRST you're practically there. — FistOfFury, Dec 02 '16 at 05:13

score 2 · Accepted Answer · answered Jan 10 '14 at 22:06

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;

score 1 · Answer 4 · answered Jan 10 '14 at 22:47

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.

I like the explanation and effort that went into this one. Thanks for this. I think the less verbose query using the Day Of Week # from user2989408 is easier to use for my purposes. — FistOfFury, Jan 05 '15 at 21:47

rhealitycheck · Answer 5 · 2014-01-10T21:39:42.743

0

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

edited Jan 10 '14 at 21:39

answered Jan 10 '14 at 21:25

rhealitycheck

650
3
8
19

1

Any particular reason you didn't chose `datepart(dw, @date)` instead of one that returns a string? – Lasse V. Karlsen Jan 10 '14 at 22:09
personally i find it more readable to get the string for the day of the week name instead of trying to remember which int corresponds to the day of the week. especially in this case where he's just trying to exclude saturday and sunday. but that's just my perspective on readability of what your results are displaying to you. – rhealitycheck Jan 13 '14 at 16:19
Will the string be english-only? – Lasse V. Karlsen Jan 13 '14 at 19:28
i believe it uses the default language of the sql server that you're on, so you'd likely need to look at the documentation to confirm. – rhealitycheck Jan 14 '14 at 17:11

score 0 · Answer 6 · answered Dec 23 '19 at 10:05

0

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.

answered Dec 23 '19 at 10:05

Abdul Qadir Memon

950
1
12
27

score 0 · Answer 7 · edited Mar 03 '21 at 15:05

0

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

edited Mar 03 '21 at 15:05

Suraj Rao

29,388
11
94
103

answered Mar 03 '21 at 14:53

sitanshu chaudhary

1

While this code may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply. – John Conde Mar 04 '21 at 00:14

How to find last weekday of current month using SQL

7 Answers7