SQL SERVER – Script/Function to Find Last Day of Month

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)

SQL DateTime, SQL Function, SQL Scripts, SQL Utility
Previous Post
SQL SERVER – ASCII to Decimal and Decimal to ASCII Conversion
Next Post
SQL SERVER – SQL Code Formatting Tools

Related Posts

13 Comments. Leave new

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

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

    Reply
  • Another method

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

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

    Reply
  • 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
  • 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.

    Reply
  • 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

    Reply
  • The code is missing a minus?

    Reply
  • Hi you can try that too :)
    SELECT DATEDIFF(DAY,GETDATE(),DATEADD(MONTH,1,GETDATE()))

    Reply

Leave a Reply