CODEWITHSUNDEEP
sql
Matt
Matt

Reputation: 4190

Return just the last day of each month with SQL

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.

Upvotes: 20

Views: 90325

Answers (9)

jw11432
jw11432

Reputation: 593

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

Upvotes: 0

Shekhar Nalawade
Shekhar Nalawade

Reputation: 127

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

Upvotes: 0

Javier Rapoport
Javier Rapoport

Reputation: 123

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.

Upvotes: 11

Amit
Amit

Reputation: 667

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

Upvotes: 0

Olumide Ibilaiye
Olumide Ibilaiye

Reputation: 1

This should work on Oracle DB 

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

Upvotes: 0

FistOfFury
FistOfFury

Reputation: 7135

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)

Upvotes: 5

Nicholas Carey
Nicholas Carey

Reputation: 74197

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.

Upvotes: 2

Aaron Digulla
Aaron Digulla

Reputation: 328556

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

Upvotes: 0

Tomalak
Tomalak

Reputation: 338158

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.

Upvotes: 36

Related Questions