Get the last day of the month in SQL

user_42304 0
Viewed 246709

I need to get the last day of the month given as a date in SQL. If I have the first day of the month, I can do something like this:

DATEADD(DAY, DATEADD(MONTH,'2009-05-01',1), -1)

But does anyone know how to generalize it so I can find the last day of the month for any given date?

tsql sql-server sql
22 Answers

Here's my version. No string manipulation or casting required, just one call each to the DATEADD, YEAR and MONTH functions:

DECLARE @test DATETIME
SET @test = GETDATE()  -- or any other date

SELECT DATEADD(month, ((YEAR(@test) - 1900) * 12) + MONTH(@test), -1)
Luke_558470

You could get the days in the date by using the DAY() function: 

dateadd(day, -1, dateadd(month, 1, dateadd(day, 1 - day(date), date)))
user_98050

Works in SQL server

Declare @GivenDate datetime
SET @GivenDate = GETDATE()

Select DATEADD(MM,DATEDIFF(MM, 0, @GivenDate),0) --First day of the month 

Select DATEADD(MM,DATEDIFF(MM, -1, @GivenDate),-1) --Last day of the month
user_73133510

Just extend your formula out a little bit:

dateadd(day, -1,
    dateadd(month, 1,
        cast(month('5/15/2009') as varchar(2)) + 
        '/1/' + 
        cast(year('5/15/2009') as varchar(4)))
user_955890

This query can also be used.

DECLARE @SelectedDate DATE =  GETDATE()

SELECT DATEADD(DAY, - DAY(@SelectedDate), DATEADD(MONTH, 1 , @SelectedDate)) EndOfMonth
user_85001100

My 2 cents:

select DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(day,(0-(DATEPART(dd,'2008-02-12')-1)),'2008-02-12')))

Raj

user_1239510

Based on the most voted answer at below link I came up with the following solution:

 declare  @mydate date= '2020-11-09';
  SELECT DATEADD(month, DATEDIFF(month, 0, @mydate)+1, -1) AS lastOfMonth

link: How can I select the first day of a month in SQL?

user_40459470

I couldn't find an answer that worked in regular SQL, so I brute forced an answer:

SELECT *
FROM orders o
WHERE (MONTH(o.OrderDate) IN ('01','03','05','07','08','10','12') AND DAY(o.OrderDate) = '31')
    OR (MONTH(o.OrderDate) IN ('04','06','09','11') AND DAY(o.OrderDate) = '30')
    OR (MONTH(o.OrderDate) IN ('02') AND DAY(o.OrderDate) = '28')
user_23362120

--## Useful Date Functions

SELECT

GETDATE() AS [DateTime],

CAST(GETDATE() AS DATE) AS [Date],

DAY(GETDATE()) AS [Day of Month],

FORMAT(GETDATE(),'MMMM') AS [Month Name],

FORMAT(GETDATE(),'MMM') AS [Month Short Name],

FORMAT(GETDATE(),'MM') AS [Month No],

YEAR(GETDATE()) AS [Year],

CAST(DATEADD(DD,-(DAY(GETDATE())-1),GETDATE()) AS DATE) AS [Month Start Date],

EOMONTH(GETDATE()) AS [Month End Date],

CAST(DATEADD(M,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE()),0)) AS DATE) AS [Previous Month Start Date],

CAST(DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE()),0)) AS DATE) AS [Previous Month End Date],

CAST(DATEADD(M,+1,DATEADD(MM, DATEDIFF(M,0,GETDATE()),0)) AS DATE) AS [Next Month Start Date],

CAST(DATEADD(D,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE())+2,0)) AS DATE) AS [Next Month End Date],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE()),0) AS DATE) AS [First Day of Current Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())+1,-1) AS DATE) AS [Last Day of Current Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())-1,0) AS DATE) AS [First Day of Last Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE()),-1) AS DATE) AS [Last Day of Last Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())+1,0) AS DATE) AS [First Day of Next Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())+2,-1) AS DATE) AS [Last Day of Next Week]
user_198862770 
---Start/End of previous Month 
Declare @StartDate datetime, @EndDate datetime

set @StartDate = DATEADD(month, DATEDIFF(month, 0, GETDATE())-1,0) 
set @EndDate = EOMONTH (DATEADD(month, DATEDIFF(month, 0, GETDATE())-1,0)) 

SELECT @StartDate,@EndDate
user_93604160
Related