Getting last Monday of the current month using T-sql

Question

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

Hooray!

Answer 1

The next select will return 1 if the current date is the last month 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 - 2. The second part adds 7 days to the current date and checks that the month has changed within 7 days (if it is not, this is not the last Monday).

Change GETDATE() to any date you want to check.

EDIT:

You can do this in a generic function and use it with any date:

 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 

Answer 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 

Answer 3

This will allow you to choose the date when the date of the last month of the month, regardless of the start date. Creating a function based on database settings is not really 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 

Answer 4

Although you already accepted a different answer, an even simpler solution (in my opinion) is to use a calendar table that has a column called IsLastMondayOfMonth or something else similar. Calendar tables are generally much easier to maintain than functions, because not only is the code much cleaner, but also when an exception occurs ("due to an unusual public holiday this year, we need to do our processing at the end of the month the next day, I “Of course, you can fix the system to do this?”), You can simply update the table to process it instead of adding potentially inconvenient logic to your code.