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. Use it any time you need the last day of month for a given date.

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)

How the Last Day of Month Query Works

The trick is easier to trust once you see the steps. First, DATEADD moves the date one month forward. Then DAY() tells you which day of that next month you landed on. Subtract that many days and you step back to the end of the original month. For 3 February 2008, one month ahead is 3 March, and going back three days lands on 29 February.

It also works for dates at the end of a month. For 31 January 2008, one month ahead is 29 February, because SQL Server stops at the last valid day. Step back 29 days and you are on 31 January again.

Watch out for one detail: the time part of the input is kept. If you pass 2008-02-13 at 1:45 PM, you get 29 February at 1:45 PM, not midnight. If you need midnight, strip the time first, or use this form, which always returns midnight: DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) + 1, 0)).

On SQL Server 2012 and later, the built in EOMONTH function does the same job in one call and returns a date with no time part. It also takes a second argument to add or subtract months, so EOMONTH(@date, -1) gives the end of the previous month.

A common mistake is filtering a datetime column with BETWEEN the first day and the last day of the month. Rows from later in the day on the last day are missed, because that last day means midnight. A safer filter is column >= first day of this month and column < first day of next month.

When you test any month end code, use a short list of dates: the first and last day of a month, 31 January, the end of February in a leap year and in a normal year, and 31 December, where the next month falls in the next year. If all of those work, you can trust the query.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.