Is Date the Last Day of the Month

Question

Is Date the Last Day of the Month

mbrady5

SSCrazy

Points: 2835
More actions
April 14, 2016 at 10:36 am

#318111

Hi,
I'd like to flag my data to let me know if a date is the last day of the month.
So, a simple flag which would indicate True or False.
Select Date, Is The Date Last Day of the month?
From Where ever
3/31/2016 | True
4/1/2016 | False
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

CASE WHEN MONTH(YourDate) <> MONTH(DATEADD(day, 1, YourDate)) THEN 'True' ELSE 'False' END

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Hugo Kornelis (4/14/2016)
CASE WHEN MONTH(YourDate) <> MONTH(DATEADD(day, 1, YourDate)) THEN 'True' ELSE 'False' END

Fixed That For You

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

A different option.

SELECT YourDate,

CASE WHEN MONTH(YourDate) <> MONTH(DATEADD(day, 1, YourDate)) THEN 'True' ELSE 'False' END,

CASE WHEN YourDate = DATEADD( MM, DATEDIFF( MM, 0, YourDate), 30) THEN 'True' ELSE 'False' END

FROM( VALUES( '3/31/2016'), ('4/1/2016'))x(YourDate);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

R.P.Rozema SSChampion Points: 12317 More actions · Answer 4

Luis Cazares (4/14/2016)
A different option.
SELECT YourDate,
CASE WHEN MONTH(YourDate) <> MONTH(DATEADD(day, 1, YourDate)) THEN 'True' ELSE 'False' END,
CASE WHEN YourDate = DATEADD( MM, DATEDIFF( MM, 0, YourDate), 30) THEN 'True' ELSE 'False' END
FROM( VALUES( '3/31/2016'), ('4/1/2016'))x(YourDate);

This 2nd suggestion works but it assumes that YourDate does not have a time component (or time must be 0:00) or it wil not work. Hugo's suggestion was simple and works for any datetime value on any month's last day. So I suggest you better use that 1st suggestion.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?