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.

Answer 1

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.

Answer 2

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

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

Answer 3

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.