find the last day of the month. Query also take care of Leap Year.

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

 

ResultSet:

LastDayOfMonth

-----------------------

2008-02-29 00:00:00.000

 

GO

DECLARE @date DATETIME

    SET @date='2007-02-03'

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date))

            AS LastDayOfMonth

ResultSet:

LastDayOfMonth

-----------------------

2007-02-28 00:00:00.000