Many web sites and blogs will tell you how to find the last day, first day of a month or year
But what if you need the month end and begin dates for a specific time period? Well, here is the code for that:
–script to get month begining & end dates for a time period
–declare all variables
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @MonthEndDate datetime
DECLARE @MonthBeginDate datetime
DECLARE @MonthlyDates Table
( MonthID INT NOT NULL IDENTITY(1,1),
MonthBeginDate SMALLDATETIME NOT NULL,
MonthEndDate SMALLDATETIME NOT NULL
)
–set the time period here
SET @StartDate = ’3/1/2009′
SET @EndDate = ’5/31/2012′
–Get the first month’s begin and end dates
SELECT @MonthBeginDate = DATEADD(dd,-(DAY(@StartDate)-1),@StartDate)
SELECT @MonthEndDate = DATEADD(mm,1,@StartDate) – DAY(DATEADD(mm,1,@StartDate))
–do the first insert into the holding table
INSERT INTO @MonthlyDates
(MonthBeginDate, MonthEndDate)
VALUES
(@MonthBeginDate, @MonthEndDate)
–use while loop to move through each month
WHILE @MonthEndDate < @EndDate
BEGIN
SELECT @MonthBeginDate = DATEADD(mm, 1, @MonthBeginDate)
SELECT @MonthEndDate = DATEADD(dd, -1, DATEADD(mm, 1, @MonthBeginDate))
INSERT INTO @MonthlyDates
(MonthBeginDate, MonthEndDate)
VALUES
(@MonthBeginDate, @MonthEndDate)
END
SELECT
MonthBeginDate,
MonthEndDate
FROM @MonthlyDates
So this code will give you all the months begin & end dates, in order. Now in this code I’m not looking at when the @StartDate or @EndDate falls in the month. For example, if I pass into the code a start date of March 10, 2009 it will return, as the first row, the begin and end dates for the month of March. If you wanted to only return dates for whole months that your range covers (in the above example that would be a start month of April), you would need to alter the code.
Oh, and it is good for leap years too!
