Return just the last day of each month with SQL

SQL Server (other DBMS will work the same or very similarly):

SELECT
  *
FROM
  YourTable
WHERE
  DateField IN (
    SELECT   MAX(DateField)
    FROM     YourTable
    GROUP BY MONTH(DateField), YEAR(DateField)
  )

An index on DateField is helpful here.

PS: If your DateField contains time values, the above will give you the very last record of every month, not the last day's worth of records. In this case use a method to reduce a datetime to its date value before doing the comparison, for example this one.

The easiest way I could find to identify if a date field in the table is the end of the month, is simply adding one day and checking if that day is 1.

where DAY(DATEADD(day, 1, AsOfDate)) = 1

If you use that as your condition (assuming AsOfDate is the date field you are looking for), then it will only returns records where AsOfDate is the last day of the month.

Use the EOMONTH() function if it's available to you (E.g. SQL Server). It returns the last date in a month given a date.

select distinct
Date 
from DateTable
Where Date = EOMONTH(Date)

Or, you can use some date math.

select distinct
Date
from DateTable
where Date = DATEADD(MONTH, DATEDIFF(MONTH, -1, Date)-1, -1)