Following query will find the last day of the month. Query also take care of Leap Year.
Script:
DECLARE @date DATETIME
SET @date='2008-02-03'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date))
AS LastDayOfMonth
GO
DECLARE @date DATETIME
SET @date='2007-02-03'
SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date))
AS LastDayOfMonth
GO
ResultSet:
LastDayOfMonth ----------------------- 2008-02-29 00:00:00.000 (1 row(s) affected) LastDayOfMonth ----------------------- 2007-02-28 00:00:00.000 (1 row(s) affected)
Reference : Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
DECLARE @date DATETIME
SET @date=’2009-02-04′
SELECT
DATEADD(mm,1,@date)-DAY(@DATE)
AS LastDayOfMonth
Doesn’t work if today IS the last day of the current month.
Thanks for this article. It helped me a lot .
:-)
Another method
DECLARE @date DATETIME
SET @date=’2008-02-03′
SELECT DATEADD(month,DATEDIFF(month,0,@date)+1,-1) AS LastDayOfMonth
Hello Pinal,
How do you calculate the last day of ANY month when you are only given the month and year, no day?
Thanks
The simple method is
declare @month int, @year int
select @month=3, @year=2009
select DATEADD(year,@year-1900,dateadd(month,@month,0))-1
Nice
also the EOMONTH function from the commericial package XLeratorDB…
Use EOMONTH to calculate the date for the last day of the month that is the indicated number of months before or after the start date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
SELECT DATEADD(MONTH,1,GETDATE())-DAY(GETDATE()) CurrentMonthLast_Day,
DATEADD(MONTH,0,GETDATE())-DAY(GETDATE())+1 CurrentMonthFirstt_Day,
DATEADD(MONTH,1,GETDATE())-DAY(GETDATE()-1) NextMonthFirst_Day
The code is missing a minus?
Ah – have no seen it hiding out on its on on my rendering.
:)
Hi you can try that too :)
SELECT DATEDIFF(DAY,GETDATE(),DATEADD(MONTH,1,GETDATE()))