Computing First and Last Day of Current and Previous Month (SQL Server)

• Download source code - 902 B

Introduction

This tip is about getting first and last day of current and previous month, a highly desired requirement in many database and reporting solutions.

There are many examples available but it is always good to explore them further.

SQL2008R2 and previous versions require work around to calculate first and last day of the month. It is very important to keep a consistent approach to calculate the first and last day of the current and previous month.

Despite the fact that SQL2012 and upward versions provide built in EOMONTH() function to calculate first day and last day of month, it is worth considering to understand how to get there without the need to use EOMONTH() because the code can be modified further for related computations.

Logic Behind Code

If you know the logic behind the code, it is relatively easier to understand and write the code.

Get First Day of Current Month

1. Subtract all days of the current month
2. Add one day to get first day

Get Last Day of Current Month

1. Get (same) current day of next month
2. Subtract all the days of next month

Get First Day of Previous Month

1. Get previous month
2. Subtract all days of the previous month
3. Add one day to get first day

Get Last Day of Previous Month

Subtract all the days of current month

Coding Example

The workaround is to use DateAdd() function to subtract days of month using Day() function according to the requirements as shown below in the coding example:

--=================================================================================================
-- (1) Get first day of current month
--=================================================================================================
-- Subtract all days of current month
DECLARE @MonthFirstDay DATETIME =_
(SELECT CONVERT(CHAR(15),DATEADD(DD,-DAY(GETDATE()),GETDATE()),106))
 
-- Add one day to get first day
SET @MonthFirstDay=@MonthFirstDay+1
 
-- Show first day of current month
SELECT @MonthFirstDay as MonthFirstDay
--================================================================================================
-- (2) Get last day of current month
--================================================================================================
-- Get (same) current day of next month
DECLARE @MonthLastDay DATETIME=(SELECT CONVERT(CHAR(15),DATEADD(MM,1,GETDATE()),106))
 
-- Subtract all the days of next month
SET @MonthLastDay=DATEADD(DD,-DAY(@MonthLastDay),@MonthLastDay)
 
-- show last day of current month
SELECT @MonthLastDay as MonthLastDay
 
--=================================================================================================
-- (3) Get first day of previous month
--=================================================================================================
-- Get previous month
DECLARE @PrevMonthFirstDay DATETIME =(SELECT CONVERT(CHAR(15),DATEADD(MM,-1,GETDATE()),106))
 
-- Subtract all days of previous month
SET @PrevMonthFirstDay=_
(SELECT CONVERT(CHAR(15),DATEADD(DD,-DAY(@PrevMonthFirstDay),@PrevMonthFirstDay),106))
 
-- Add one day to get first day
SET @PrevMonthFirstDay=@PrevMonthFirstDay+1
 
-- Show first day of previous month
SELECT @PrevMonthFirstDay as PrevMonthFirstDay
 
--=================================================================================================
-- (2) Get last day of previous month
--=================================================================================================
-- Subtract all the days of current month
DECLARE @PrevMonthLastDay DATETIME=_
(SELECT CONVERT(CHAR(15),DATEADD(DD,-Day(GETDATE()),GETDATE()),106))
 
-- show last day of current month
SELECT @PrevMonthLastDay as PrevMonthLastDay

Points of Interest

It is recommended to convert these scripts into procedures to avoid repetition of the code and then relying on calling the stored procedures whenever required.

Think of a transactional monthly report where you cannot use built-in function EOMonth() due to version limitation then it is handy to pre-calculate beginning and end of the current month using the above scripts (better if turned into procedures).

References

1. http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/
2. https://sqlmate.wordpress.com/2012/08/24/get-first-and-last-day-of-month-in-sql-2012-and-sql-2008-and-earlier/