How do I check whether the input date's DAY is the last day of the given month in SQL?

Question

DECLARE @Date DATE = '2/28/2014'

I need to test whether the DAY in the DATE above is the LAST day of the month and it should work for all months in the year.

If it's true, then I need to return a true, if not, then false.

E.g.

SET @Date = '2/27/2014'

This should return FALSE.

SET @Date = '12/31/2014'

This should return TRUE.

I know you can manipulate this based on month but I'm just wondering whether there is an easy way to do it.

For SQL Server 2012 or later the [EOM](https://msdn.microsoft.com/en-us/library/hh213020.aspx) function should be what you're looking for. — Dave Mason, Jun 15 '15 at 22:01

score 8 · Accepted Answer · answered Jun 15 '15 at 22:03

An easy way that works in almost any version of SQL Server is:

select (case when month(@date) <> month(dateadd(day, 1, @date))
             then 'true' else 'false'
         end)

That is, add a day and see if you are in the same month. This works for leap years, and Dec 31 and so on.

score 2 · Answer 2 · answered Jun 15 '15 at 22:10

2

This will return 1 if its the last day of the month, and 0 otherwise

DATEDIFF(month,@date,@date+1)

answered Jun 15 '15 at 22:10

RBarryYoung

55,398
14
96
137

Though the +1 only works with the legacy datatypes not `date` IIRC. So that would need to be `DATEDIFF(month,@date,dateadd(day,1,@date))` – Martin Smith Jun 15 '15 at 22:12
@hmmm, you may be right, i didn't check. CAST to datetime should fix that if so. – RBarryYoung Jun 15 '15 at 22:13

score 2 · Answer 3 · answered Jun 15 '15 at 22:22

As no one has given the 2012+ answer as an answer yet...

SELECT IIF(EOMONTH(@date) = @date, 'Yes','No')

As an aside you should use an unambiguous format for date literals such as ISO yyyy-mm-dd to avoid surprises when the code is executed under a login with different default options.

How do I check whether the input date's DAY is the last day of the given month in SQL?

3 Answers3