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 (http://blog.SQLAuthority.com)
[...] SQL SERVER – Script/Function to Find Last Day of Month SQL SERVER – Query to Find First and Last Day of Current Month [...]
DECLARE @date DATETIME
SET @date=’2009-02-04′
SELECT
DATEADD(mm,1,@date)-DAY(@DATE)
AS LastDayOfMonth
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
[...] More on Leap Yer: Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function Date and Time Functions – EOMONTH() – A Quick Introduction Script/Function to Find Last Day of Month [...]