Last week I wrote a blog post about SQL SERVER – List the Name of the Months Between Date Ranges. It was written in the response to the question – How to list the name of the months between two date ranges? In the original blog post, I made a small error, hence I corrected that in the follow up blog post. Thanks to Sanjay Monpara for helping me correct the error. There are some amazing comments received in the original blog posts.
Here is the alternate solution proposed by Miladin Joksic and Peter Lalovsky. Miladin Joksic wrote the original script and Peter Lalovsky further improved it. It also retrieves the name of the months between date ranges as well have provided additional details like year as well month in numerics.
-- Count the months of @DateStart and @DateEnd
DECLARE @DateStart DATETIME = '2014-07-17' -- 2014 July
DECLARE @DateEnd DATETIME = '2015-07-08'; -- 2015 July
WITH Dates AS
(
SELECT DATEADD(DAY, -(DAY(@DateStart) - 1), @DateStart) AS [Date]
UNION ALL
SELECT DATEADD(MONTH, 1, [Date])
FROM Dates
WHERE [Date] < DATEADD(DAY, -(DAY(@DateEnd) - 1), @DateEnd)
)
SELECT
YEAR([Date]) AS [Year]
, MONTH([Date]) AS [MonthNumber]
, RIGHT('0' + CAST(MONTH([Date]) AS VARCHAR(2)), 2) AS [MonthNumberLeadingZero]
-- Leading zero
, DATENAME(MM, [Date]) AS [MonthName]
FROM Dates;
Thanks for amazing contribution to the original problem.
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
–>Another way using user defined table valued function ‘daterange’
–###############
DECLARE @StartDate DATETIME, @EndDate DATETIME;
SELECT @StartDate = ‘2014-07-17’, @EndDate = ‘2015-07-08’;
SELECT datename(year,dates) as Year,
datepart(mm,dates) AS MonthNumber,
datename(month,dates) AS MonthName
from dbo.daterange(‘m’,@StartDate,@EndDate);
–###############
Output:
Year MonthNumber MonthName
2014 7 July
2014 8 August
2014 9 September
2014 10 October
2014 11 November
2014 12 December
2015 1 January
2015 2 February
2015 3 March
2015 4 April
2015 5 May
2015 6 June
–>–Using daterange table valued function (from my utility collection)—-
create FUNCTION [dbo].[DateRange] (@Increment CHAR(1),@StartDate DATETIME,@EndDate DATETIME)
RETURNS @SelectedRange TABLE(Dates DATETIME)
AS
BEGIN
;WITH cteRange (DateRange) AS (
SELECT @StartDate
UNION ALL
SELECT
CASE
WHEN @Increment = ‘h’ THEN DATEADD(hh, 1, DateRange)
WHEN @Increment = ‘d’ THEN DATEADD(dd, 1, DateRange)
WHEN @Increment = ‘w’ THEN DATEADD(ww, 1, DateRange)
WHEN @Increment = ‘m’ THEN DATEADD(mm, 1, DateRange)
WHEN @Increment = ‘y’ THEN DATEADD(yy, 1, DateRange)
END
FROM cteRange
WHERE DateRange <=
CASE
WHEN @Increment = 'h' THEN DATEADD(hh, -1, @EndDate)
WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
WHEN @Increment = 'y' THEN DATEADD(yy, -1, @EndDate)
END)
INSERT INTO @SelectedRange (Dates)
SELECT DateRange
FROM cteRange
OPTION (MAXRECURSION 3660);
RETURN
END
Hi, I think a simpler way of doing this would be to use a while loop and a temp table as follows:
DECLARE @DateStart DATETIME = ‘2014-07-17’, @DateEnd DATETIME = ‘2015-07-08’;
CREATE TABLE #MonthEg(YearNo INT, MonthNo INT, MonthsName VARCHAR(10))
WHILE @DateStart <= @DateEnd
BEGIN
INSERT #MonthEg VALUES (YEAR(@DateStart), MONTH(@DateStart), DATENAME(MONTH,@DateStart))
SET @DateStart=DATEADD(mm,1,@DateStart)
print @DateStart
END
SELECT YearNo AS [Year],
MonthNo AS [MonthNumber],
RIGHT('0' + CAST(MonthNo AS VARCHAR(2)), 2) AS [MonthNumberLeadingZero],
MonthsName AS[MonthName]
FROM #MonthEg
DROP TABLE #MonthEg
–SELECT DATENAME(month,’2007-10-30′)
–SELECT DATENAME(month,’2008-09-30′)
declare @start DATE = ‘2014-07-17’
declare @end DATE = ‘2015-07-08’
;WITH MyCTE([Date]) AS(
SELECT @start
UNION ALL
SELECT dateadd(month,1,[Date]) AS dates from MyCTE where dateadd(day,1,[Date])<@end
)
Select YEAR([Date]) AS [Year]
, MONTH([Date]) AS [MonthNumber]
, RIGHT('0' + CAST(MONTH([Date]) AS VARCHAR(2)), 2) AS [MonthNumberLeadingZero]
— Leading zero
, DATENAME(MM, [Date]) AS [MonthName] from MyCTE;