Return only the last day of every month using SQL

Question

I have a table that contains several records for each day of the month, over several years. Can someone help me write a query that returns only the last day of each month.

Answer 1

SQL Server (other DBMSs will work the same or very accurate):

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

The DateField index is useful DateField .

PS: If your DateField contains time values, the above will give you the most recent record of each month, and not the last cost of records for the day. In this case, use the method to reduce the datetime value to its date value before performing a comparison, for example this one .

Answer 2

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

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

In a nutshell:

• From your point of reference
• Add 1 month,
• Then subtract its day of the week in days from the obtained value.

Voila! You have the last day of the month containing your checkpoint in time.

Getting the first day of the month is easier:

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

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

Disabling / normalizing the extraneous component remains as an exercise for the reader.

Answer 3

The easiest way to determine if there is a date field in the table at the end of the month, simply adds one day and checks if this day is 1.

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

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

Answer 4

Use the EOMONTH () function if available to you (e.g. SQL Server). It returns the last date of the month with the date.

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

Or you can use math with a date.

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

Answer 5

This should work with 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 everything worked out perfectly. I also need the maximum date for the current month . That's what I'm doing. Pay attention to the last date of July, which is 24th place. I pulled it on 07/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 applies to Oracle DB

Answer 8

An easy way to get the last day of the month is to get the first day of the next month and subtract 1.