Getting the last Monday of the current month using T-sql

Question

I know this is quite a generic question but does anyone know a good way of checking if the date is the last monday of the month using T-SQL. I need to use it in a stored procedure to determine if the stored procedure returns data or does nothing.

Cheers!

Klaus Byskov Pedersen · Accepted Answer · 2012-02-28T12:37:06.393

The following select will return 1 if the current date is the last monday of the month, and 0 if not.

select 
 case 
 when datepart(dw, GETDATE()) = 2 and DATEPART(month, DATEADD(day, 7, GETDATE())) <> DATEPART(month, GETDATE())
 then 1
 else 0
 end

datepart(dw, GETDATE()) returns the day of the week. Monday is 2. The second part adds 7 days to the current date and checks that within 7 days the month has changed (if it does not, it is not the last monday).

Change the GETDATE()'s to any date you want to check.

EDIT:

You can make it into a generic function and use it with any date you like:

CREATE FUNCTION 
IsLastMondayOfMonth(@dateToCheck datetime)
RETURNS bit
AS
BEGIN
DECLARE
@result bit

SELECT @result =
    CASE  
       WHEN datepart(dw, @dateToCheck) = 2 AND DATEPART(month, DATEADD(day, 7, @dateToCheck)) <> DATEPART(month, @dateToCheck)
    THEN 1
 ELSE 0
 END
 RETURN @result
END

This works perfectly, thank you. If the select statement returns 0 I won't return any data because this data is only needed on the last monday of the month and not any other Monday. — nick gowdy, Feb 28 '12 at 12:35

score 2 · Answer 2 · answered Feb 28 '12 at 12:30

2

Maybe something like this:

DECLARE @YourDate DATETIME='2012-02-25'
SELECT
    CASE 
        WHEN @YourDate = DATEADD(wk, DATEDIFF(wk,0,DATEADD(month,DATEDIFF(MONTH,0,@YourDate),30)),0)
        THEN 1
        ELSE 0
    END

answered Feb 28 '12 at 12:30

Arion

31,011
10
70
88

Really good solution and I don't agree with the choice of solution. Although more complex to read, your query is faster and it doesn't rely on @@datefirst. +1 from me – t-clausen.dk Feb 28 '12 at 13:43

t-clausen.dk · Answer 3 · 2012-03-02T23:03:46.740

0

This will select the date when the date is the last monday of the month regardless of firstdate. Making a function that rely on the database settings, is really not good practice.

select d
from (select '2012-02-20' d union all select '2012-02-27' union all select '2012-02-28') a
where datepart(day, dateadd(day, 7 ,d)) < 8 and datediff(day, 0, d) %7 = 0

edited Mar 02 '12 at 23:03

answered Feb 28 '12 at 13:33

t-clausen.dk

43,517
12
59
92

score 0 · Answer 4 · edited May 23 '17 at 10:34

Although you have already accepted another answer, an even simpler solution (in my opinion) is to use a calendar table that has a column called IsLastMondayOfMonth or something similar. Calendar tables tend to be much easier to maintain than functions because not only is the code much cleaner but also when an exception comes along ("because of an unusual public holiday this year we need to do our month-end processing one day later, I'm sure you can fix the system to do that?") you can just update a table to handle it instead of adding potentially awkward logic to your code.

Getting the last Monday of the current month using T-sql

4 Answers4