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)
pinaldave
[...] 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