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)

11 thoughts on “SQL SERVER – Script/Function to Find Last Day of Month”

1. Pingback: SQL SERVER - Find Last Day of Any Month - Current Previous Next Journey to SQL Authority with Pinal Dave

2. DECLARE @date DATETIME
   SET @date=’2009-02-04′
   SELECT
   DATEADD(mm,1,@date)-DAY(@DATE)
   AS LastDayOfMonth

   Reply

   • Doesn’t work if today IS the last day of the current month.

     Reply

3. Thanks for this article. It helped me a lot .
   :-)

   Reply

4. Another method

   DECLARE @date DATETIME
   SET @date=’2008-02-03′

   SELECT DATEADD(month,DATEDIFF(month,0,@date)+1,-1) AS LastDayOfMonth

   Reply

5. Hello Pinal,

   How do you calculate the last day of ANY month when you are only given the month and year, no day?

   Thanks

   Reply

   • The simple method is

     declare @month int, @year int
     select @month=3, @year=2009
     select DATEADD(year,@year-1900,dateadd(month,@month,0))-1

     Reply

     • Nice

       Reply

6. Pingback: SQL SERVER – Function: Is Function – SQL in Sixty Seconds #004 – Video « SQL Server Journey with SQL Authority

7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

8. also the EOMONTH function from the commericial package XLeratorDB… http://tinyurl.com/eomonth

   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.

   Reply