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

问题:

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.

回答1:

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

回答2:

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.

回答3:

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