SQL – Compute Last Day of Month

A simple method to calculate the last day of the month in SQL.


Reports are often scoped to date ranges of a year, quarter, month or day. Of these ranges, only the end date of the month varies between 28 and 31 days. Years always start on January 1 and end on December 31. Quarters are January 1 thru March 31, April 1 thru June 31, July 1 thru September 30 and October 1 thru December 31.

For monthly reports you need to determine the last day of the month. This can be difficult because a simple look up table of dates will fail due to leap years and calculating leap year adds more code for you to maintain.


Here is a simple solution I came up with to let the database determine the end of the month and deal with leap years.

DECLARE @date DATE = '10/22/2020'

-- 1) Calculate the number of days between the current date and first day of the month.
--    (example: if date is ’10/22/2020′ then @day = 21). 
DECLARE @days INT = DATEPART(DAY, @date) - 1

-- 2) Calculate the first day of this month by subtracting @days from the current date.
--    (example: ’10/22/2020′ – 21 days = ’10/1/2020′)
DECLARE @firstDayOfThisMonth DATE = DATEADD(DAY, (-1 * @days), @date)

-- 3) Calculate the first day of next month by incrementing @firstDayOfThisMonth by one month.
--    (example: ’10/1/2020′ + 1 MONTH = ’11/1/2020′)
DECLARE @firstDayOfNextMonth DATE = DATEADD(MONTH, 1, @firstDayOfThisMonth)

-- 4) Subtract one day from @firstDayOfNextMonth to get the @lastDayOfThisMonth.
--    (example: ’11/1/2020′ – 1 DAY = ’10/31/2020′)
DECLARE @lastDayOfThisMonth DATE = DATEADD(DAY, -1, @firstDayOfNextMonth)


Take advantage of the libraries and tools you have to reduce and simplify the code you write. The less code you write, the less you need to maintain.