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

Here is another interesting question, I received the other day.

“How to list the name of the months between two date ranges?”

Very interesting question. I had no script ready for it so I asked my friend who used to my co-worker earlier and he has sent me the script which is listed below.

DECLARE @StartDate  DATETIME,
@EndDate    DATETIME;
SELECT @StartDate = '20140301' -- March
,@EndDate   = '20140901'; -- September
SELECT  DATENAME(MONTH, DATEADD(MONTH, nos.monthnos, @StartDate)-1) AS MonthName
FROM    (SELECT 1 monthnos UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) nos
WHERE     nos.monthnos <= DATEDIFF(MONTH, @StartDate, @EndDate)+1;

SQL SERVER - List the Name of the Months Between Date Ranges monthsbetween

Above script does return the name of the months between date ranges. Let me know if there is any other way to achieve the same.

Please note: This blog post is modified based on the feedback of SQL Expert Sanjay Monpara. Thank you Sanjay!

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

Previous Post
SQL SERVER -Fix Error – Cannot open backup device. Operating system error 5(Access is denied.)
Next Post
Hey DBA – Baselines and Performance Monitoring – Why? – Notes from the Field #058

Related Posts

24 Comments. Leave new

  • minor change in your script to include March month in output.
    —————————————————————————-
    DECLARE @StartDate DATETIME,
    @EndDate DATETIME;
    SELECT @StartDate = ‘20140301’ — March
    ,@EndDate = ‘20140901’; — September
    SELECT DATENAME(MONTH, DATEADD(MONTH, nos.monthnos-1, @StartDate)) AS MonthName
    FROM (SELECT 1 monthnos UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
    UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) nos
    WHERE nos.monthnos <= DATEDIFF(MONTH, @StartDate, @EndDate)+1;

    Reply
  • DECLARE @StartDate DATETIME,
    @EndDate DATETIME;
    SELECT @StartDate = ‘20140301’ — March
    ,@EndDate = ‘20140901’; — September

    SELECT datename(month,dates) AS MonthName
    from dbo.daterange(‘m’,@StartDate,@EndDate);

    MonthName
    —————
    March
    April
    May
    June
    July
    August
    September

    –>———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
  • Miladin Joksic
    December 9, 2014 2:34 pm

    I am using similar script with CTE:
    DECLARE
    @StartDate datetime = ‘2014-07-01’
    DECLARE
    @EndDate datetime = ‘2015-07-01’;
    WITH dates
    AS (SELECT dt = DATEADD(DAY,-(DAY(@StartDate) – 1),@StartDate)
    UNION ALL
    SELECT DATEADD(MONTH,1,dt)
    FROM dates
    WHERE dt < DATEADD(DAY,-(DAY(DATEADD(DAY,-1,@EndDate)) – 1),DATEADD(dd,-1,@EndDate)))
    SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,dt),0)AS FirstDayOfTheMonth
    ,DATEADD(DAY,-(DAY(DATEADD(MONTH,1,dt))),DATEADD(MONTH,1,dt))AS LastDayOfTheMonth
    ,DATENAME(MONTH, DATEADD(MONTH,DATEDIFF(MONTH,0,dt),0)) AS MonthName
    FROM dates

    Reply
  • Based on Miladin Joksic

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

    Reply
  • Siddharth Pandey (@Siddharthp28)
    December 10, 2014 1:03 am

    This looks interesting. I’m going to try this out soon. ta

    Reply
  • isaiasnas (@_isaiasnas)
    December 10, 2014 7:54 pm

    DECLARE @S DATETIME,@E DATETIME;
    SET @S = ‘20140301’; –START
    SET @E = ‘20141130’; –END

    ;WITH T AS (SELECT 1 [N] UNION ALL SELECT N+1 FROM T WHERE N<=12)
    SELECT DATENAME(MONTH,DATEADD(MONTH,T.N,-1))[NAME] FROM T WHERE T.N BETWEEN MONTH(@S) AND MONTH(@E);

    Reply
  • DECLARE @StartDate DATETIME,
    @EndDate DATETIME;
    SELECT @StartDate = ‘20140301’ — March
    ,@EndDate = ‘20140901’; — September

    ;With CTE as
    (
    SELECT @StartDate AS [StartDate], DATENAME(MONTH, @StartDate) AS [MonthName]

    UNION ALL
    SELECT DATEADD(MONTH, 1, StartDate), DATENAME(MONTH, DATEADD(MONTH, 1, StartDate)) FROM CTE
    WHERE [StartDate] <=@EndDate
    )
    SELECT * FROM CTE

    Reply
    • isaiasnas (@_isaiasnas)
      December 19, 2014 11:54 pm

      does not work right, is bringing a month name more if the starting date is the first day of the month.

      Reply
      • instead of [StartDate] <=@EndDate,make it [StartDate] <@EndDate.Or give example where it i not working .

  • My attempt using table valued function:

    create function MonthList
    (
    @stdt date,@enddt date
    )
    Returns @temp table(MonthList varchar(100))
    as
    begin
    Declare @cnt int
    Declare @totmonths int
    Declare @month varchar(100)
    set @cnt =0
    select @totmonths=DATEDIFF(Month,@stdt,@enddt)
    while(@cnt<=@totmonths)
    begin
    select @month =Datename(MM,DATEADD(MM,@cnt,@stdt))
    insert into @temp
    values(@month)
    set @cnt=@cnt+1
    End
    return
    End

    Thank You.

    Reply
  • thank you very much PINAL DAVE. i was finding this. your blog is very usefull. when i got stuck with SQL stuff. i always visit to your site for finding the solution.this time also i got solution. thank you for offering those kind of amazing stuff to us. you are expert. you are Genius.i appriciate your work.thank you again.

    Reply
  • Benigno Geronimo
    December 2, 2015 9:19 pm

    set language ‘SPANISH’
    DECLARE @table table(fechaDesde datetime , fechaHasta datetime )
    INSERT @table VALUES(‘20151231’ , ‘20161231’);
    WITH x AS
    (
    SELECT DATEADD( m , 1 ,fechaDesde ) as fecha FROM @table
    UNION ALL
    SELECT DATEADD( m , 1 ,fecha )
    FROM @table t INNER JOIN x ON DATEADD( m , 1 ,x.fecha ) <= t.fechaHasta
    )
    SELECT LEFT( CONVERT( VARCHAR, fecha , 112 ) , 6 ) as Periodo_Id
    ,DATEPART ( dd, DATEADD(dd,-(DAY(fecha)-1),fecha)) Num_Dia_Inicio
    ,DATEADD(dd,-(DAY(fecha)-1),fecha) Fecha_Inicio
    ,DATEPART ( mm , fecha ) Mes_Id
    ,DATEPART ( yy , fecha ) Anio
    ,DATEPART ( dd, DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha))) Num_Dia_Fin
    ,DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha)) ultimoDia
    ,datename(MONTH, fecha) mes
    ,'Q' + convert(varchar(10), DATEPART(QUARTER, fecha)) Trimestre_Name
    FROM x
    OPTION(MAXRECURSION 0)

    Reply
  • Ghanshyam Bishnoi
    March 7, 2016 12:38 pm

    try it also work fine doesn’t need to union or else to retrieve the months number used system itself
    DECLARE @StartDate DATE,
    @EndDate DATE;
    SELECT @StartDate = ‘20140301’ — March
    ,@EndDate = ‘20140901’; — September
    SELECT DATENAME(MONTH, DATEADD(MONTH, nos.monthnos, @StartDate)) AS MonthName
    FROM (select number monthnos from master..spt_values
    WHERE Type = ‘P’ and number between 1 and 12
    ) nos
    WHERE nos.monthnos <= DATEDIFF(MONTH, @StartDate, @EndDate)+1;

    Reply
  • try dis

    DECLARE @StartDate DATETIME,
    @EndDate DATETIME;

    SELECT @StartDate = ‘20140101’
    ,@EndDate = ‘20141201’;

    SELECT DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
    FROM master.dbo.spt_values x
    WHERE x.type = ‘P’
    AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

    Reply
  • A less verbose solution, although will only work over approx 170 years.

    DECLARE @START DATE = ’01-JUL-2015′, @END DATE = ’03-AUG-2016′

    SELECT DATENAME(MONTH ,DATEADD(MONTH, number, @START)) FROM MASTER..spt_values
    WHERE type = ‘P’ AND number BETWEEN 0 AND DATEDIFF(MONTH, @START, @END)

    Reply
  • Your friend recommended wrong hardcoded decision. Code does not works with start date ‘2013-02-03’
    CTE is not decision. Max default recursion level 100. Increase default recursion level – not solution too.
    Not nice, but working solution:

    DECLARE @StartDate DATETIME,
    @EndDate DATETIME;
    SELECT @StartDate = ‘20140301’ — March
    ,@EndDate = ‘20140901’; — September

    DECLARE @year_t TABLE(
    [dt] DATETIME
    );

    WHILE (@StartDate <= @EndDate)
    BEGIN

    INSERT INTO @year_t(
    [dt]
    )
    VALUES(
    @StartDate
    );

    SET @StartDate = DATEADD(MONTH, 1, @StartDate);

    END

    SELECT
    [MonthName] = DATENAME(MONTH, [dt])
    FROM
    @year_t;

    Reply
  • Hi colleagues,
    Using master.*.* objects is not decision. At most cases you do not have acces rights to these objects, so, you cannot use them in your code

    Reply
  • Thanks for this. It helped me a lot.

    Reply

Leave a Reply

Menu