CODEWITHSUNDEEP
sql-server
geoff swartz
geoff swartz

Reputation: 5977

How to get row with last day of month in Sql Server query

Given a table with a single row for each day of the month, how can I query it to get the row for the last day of each month?

Upvotes: 1

Views: 1275

Answers (3)

LunarSage
LunarSage

Reputation: 285

Try adapting the following query. The SELECT statement within the IN clause choses the dates for the outer query to return.

SELECT *
FROM myTable 
WHERE DateColumn IN 
(
    SELECT MAX(DateColumn)
    FROM myTable
    GROUP BY YEAR(Datecolumn), MONTH(DateColumn)
)

Upvotes: 2

Rigerta
Rigerta

Reputation: 4039

You need to select from your table where the YourDateColumn field of the record equals the last date of the month YourDateColumn belongs to: 

SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, YourDateColumn )+1,0)) AS DATE)

Upvotes: 0

Praveen ND
Praveen ND

Reputation: 560

Try to make use of below query:

    DECLARE @Dates Table (ID INT, dt DATE)
    INSERT INTO @Dates VALUES
    (1,'2017-02-01'),
    (2,'2017-02-03'),
    (3,'2017-02-04'),
    (4,'2017-03-03'),
    (5,'2017-04-03'),
    (6,'2017-04-04')

    SELECT MAX(dt) AS LastDay FROM @Dates GROUP BY DATEPART(MONTH,dt)

OUTPUT

LastDay
2017-02-04
2017-03-03
2017-04-04

OR

SELECT DATEPART(MONTH,dt) AS [MONTH],MAX(DATEPART(DAY,dt)) AS LastDay FROM @Dates GROUP BY DATEPART(MONTH,dt)

MONTH   LastDay
   2    4
   3    3
   4    4

Upvotes: 0

Related Questions