SQL - How to tell if a date is the last day of the month?

Question

I have found a few posts (ex. SQL Query to find the last day of the month , Get the last day of the month in SQL) to get the last day of the month, but is there a way to determine if a date is the last day of the month?

For example, if I had a list of these dates-

8/29/2015   --fail

8/30/2015   --fail

8/31/2015   --pass

9/1/2015    --fail

How could I write a query to determine which dates pass / fail? I could not simply test if DAY() = 31, because the months do not all have the same number of days in them.

If you dateadd() a day to date you are testing, you might check if resulting day equals 1. — Nikola Markovinović, Aug 21 '15 at 22:39
possible dupe http://stackoverflow.com/questions/16646585/sql-query-to-find-the-last-day-of-the-month — gordy, Aug 21 '15 at 22:48
@gordy those were the exact questions I have been finding... they are about getting the last day of the month (ie using the EOMONTH() function), but what I am trying to achieve is to test if a given date is the last day of the month. — John Smith, Aug 21 '15 at 22:50
@iliketocode it's trivial to get from "getting the last day of the month" to "test if a given date is the last day of the month" isn't it? examples for doing it every which way are among the answers in the submitted question. The accepted answer here appears in the paq as well.. so vote to close? It's raining downvotes in here anyways.. — gordy, Aug 22 '15 at 00:06
SELECT (date_trunc('MONTH', Current_Date) + INTERVAL '1 MONTH - 1 day')::DATE; — Sajeev, Jun 12 '16 at 11:08

score 13 · Accepted Answer · answered Aug 21 '15 at 23:03

13

If you are using SQL Server 2012 or above, use EOMONTH:

SELECT  my_date,
        IsEndOfMonth = IIF(my_date = EOMONTH(my_date), 1, 0) 
FROM    my_table

answered Aug 21 '15 at 23:03

Code Different

90,614
16
144
163

very clever.. didn't think of that one – John Smith Aug 21 '15 at 23:04

score 4 · Answer 2 · answered Aug 21 '15 at 22:42

There are two simple ways that come to mind. First, would be:

select
testDate
, case 
   when month(testDate) <> month(dateadd(d,1,testDate))
      then 'pass'
   else 'fail'
  end endOfMonth
from tblTest

This tests if 1 day after the testDate falls on a month other than the month testDate is currently on. If so, testDate must be the last day of its current month.

The second would be:

select
testDate
, case 
   when day(dateadd(d,1,testDate)) = 1
      then 'pass'
   else 'fail'
  end endOfMonth
from tblTest

This is a similar test, but in a different way. If 1 day after testDate is the first of a month, then testDate must be the last day of the month.

score 1 · Answer 3 · answered Aug 21 '15 at 22:47

1

Try this

SELECT 
  CASE
    WHEN [DateColumn] = (CAST(DATEADD(MONTH, DATEDIFF(MONTH,0,[DateColumn])+1,0)-1 AS DATE)) 
      THEN 1
    ELSE 0
 END AS EOM
FROM CardInfo

answered Aug 21 '15 at 22:47

BJones

2,450
2
17
25

Ian · Answer 4 · 2022-12-30T17:14:08.980

0

My favorite way in SQL Server 2012 or above when dealing with a datetime:

declare @date datetime = GETUTCDATE();
select case when @date > EOMONTH(@date) then 1 else 0 end;

edited Dec 30 '22 at 17:14

answered Dec 30 '22 at 17:06

Ian

4,169
3
37
62

SQL - How to tell if a date is the last day of the month?

4 Answers4

Linked