A simple method to calculate the last day of the month in SQL.
Issue
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.
Solution
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)
Conclusion
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.
