Return just the last day of each month with SQL

Question

I have a table that contains multiple records for each day of the month, over a number of years. Can someone help me out in writing a query that will only return the last day of each month.

Answer 1

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.

Answer 2

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.

Answer 3

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)

Answer 4

In SQL Server, this is how I usually get to the last day of the month relative to an arbitrary point in time:

select dateadd(day,-day(dateadd(month,1,current_timestamp)) , dateadd(month,1,current_timestamp) )

In a nutshell:

1. From your reference point-in-time,
2. Add 1 month,
3. Then, from the resulting value, subtract its day-of-the-month in days.

Voila! You've the the last day of the month containing your reference point in time.

Getting the 1st day of the month is simpler:

select dateadd(day,-(day(current_timestamp)-1),current_timestamp)

1. From your reference point-in-time,
2. subtract (in days), 1 less than the current day-of-the-month component.

Stripping off/normalizing the extraneous time component is left as an exercise for the reader.

Answer 5

This should work on Oracle DB

select  distinct last_day(trunc(sysdate - rownum)) dt
    from dual
  connect by rownum < 430
   order by 1

Answer 6

I did the following and it worked out great. I also wanted the Maximum Date for the Current Month. Here is what I my output is. Notice the last date for July which is 24th. I pulled it on 7/24/2017, hence the result

Year    Month   KPI_Date
2017    4       2017-04-28
2017    5       2017-05-31
2017    6       2017-06-30
2017    7       2017-07-24


    SELECT  B.Year ,
    B.Month ,
    MAX(DateField) KPI_Date
FROM    Table A
    INNER JOIN ( SELECT  DISTINCT
                        YEAR(EOMONTH(DateField)) year ,
                        MONTH(EOMONTH(DateField)) month
                 FROM   Table
               ) B ON YEAR(A.DateField) = B.year
                      AND MONTH(A.DateField) = B.Month
GROUP BY B.Year ,
    B.Month 

Answer 7

SELECT * FROM YourTableName WHERE anyfilter 
AND "DATE" IN (SELECT MAX(NameofDATE_Column) FROM YourTableName WHERE
anyfilter GROUP BY
TO_CHAR(NameofDATE_Column,'MONTH'),TO_CHAR(NameofDATE_Column,'YYYY'));

Note: this answer does apply for Oracle DB

Answer 8

Here's how I just solved this. day_date is the date field, calendar is the table that holds the dates.

SELECT cast(datepart(year, day_date) AS VARCHAR) 
+ '-' 
+ cast(datepart(month, day_date) AS VARCHAR) 
+ '-' 
+ cast(max(DATEPART(day, day_date)) AS VARCHAR) 'DATE'
FROM calendar
GROUP BY datepart(year, day_date)
    ,datepart(month, day_date)
ORDER BY 1

Answer 9

A simple way to get the last day of month is to get the first day of the next month and subtract 1.