Search

Dec 10, 2012

Query to Find First and Last Day of Previous, Current and Next Month - SQL Server

Hello All,

This is normal requirement to find First and Last day of Month. I got one in which I have to find not only current month or given month but Previous and Next month as well.

Following query will gives start date and end date for previous, current and next month respectively. That is First Day of Previous Month, Last Day of Previous Month, First Day of Current Month, Last Day of Current Month, First Day of Next Month and Last Day of Next Month.

DECLARE @PreviousMonthFirstDay DATETIME
DECLARE @PreviousMonthLastDay DATETIME
DECLARE @CurrentMonthFirstDay DATETIME
DECLARE @CurrentMonthLastDay DATETIME
DECLARE @NextMonthFirstDay DATETIME
DECLARE @NextMonthLastDay DATETIME

DECLARE @CurrentDate AS DATETIME = CONVERT( DATETIME, CONVERT( DATE, GetDate() ) )

SELECT @CurrentMonthFirstDay = DATEADD(dd, -(DAY(@CurrentDAte)) + 1, @CurrentDate)
SELECT @CurrentMonthLastDay = DATEADD(d, -1, DATEADD(mm, 1, @CurrentMonthFirstDay))

SELECT @PreviousMonthFirstDay = DATEADD(m, -1, @CurrentMonthFirstDay)
SELECT @PreviousMonthLastDay = DATEADD(d, -1, @CurrentMonthFirstDay)

SELECT @NextMonthFirstDay = DATEADD(d, 1, @CurrentMonthLastDay)
SELECT @NextMonthLastDay = DATEADD(d, -1, DATEADD(mm, 1, @NextMonthFirstDay))

SELECT
@PreviousMonthFirstDay PreviousMonthFirstDay
,@PreviousMonthLastDay PreviousMonthLastDay
,@CurrentMonthFirstDay CurrentMonthFirstDay
,@CurrentMonthLastDay CurrentMonthLastDay
,@NextMonthFirstDay NextMonthFirstDay
,@NextMonthLastDay NextMonthLastDay

Output as of 12-Dec-2012image
You can make this more generic by replacing GetDate() with perticular date while setting up value of @CurrentDate