SQL SERVER – List the Name of the Months Between Date Ranges – Part 2

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.

Solarwinds
-- 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;

SQL SERVER -  List the Name of the Months Between Date Ranges - Part 2 betweendaysmonth

Thanks for amazing contribution to the original problem.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – List the Name of the Months Between Date Ranges – Correction
Next Post
SQL SERVER – Puzzle – Why Decimal is Rounded Up?

Related Posts

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

    Reply
  • 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

    Reply
  • –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;

    Reply

Leave a Reply

Menu