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;
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)
26 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;
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
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
Your code does not work
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;
This looks interesting. I’m going to try this out soon. ta
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);
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
does not work right, is bringing a month name more if the starting date is the first day of the month.
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.
Deepti,
Fantastic query! Thanks for sharing.
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.
Thanks Shehan.
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)
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;
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);
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)
This is cool. Works better than the original script of Pinal Dev. If we give more than 12 months, Pinal dev’s code gives 12 months only.
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;
Thanks for sharing.
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
spt_values is a special one. everyone would have access to it, I think.
Thanks for this. It helped me a lot.
CREATE TABLE #T (ID INT, ActiveFrom date, ActiveTo date)
truncate table #t
INSERT INTO #T VALUES
(1, Convert(DATE, ‘2020-01-25′),Convert(DATE,’2020-02-04’))
,(2, Convert(DATE, ‘2020-02-23′),Convert(DATE,’2020-02-26’))
,(3, Convert(DATE, ‘2020-06-15′),Convert(DATE,’2020-09-20’))
,(4, Convert(DATE, ‘2020-11-28′),Convert(DATE,’2022-02-06’))
Select * From #T
;with cte as (Select 1 as number
union all
Select number + 1 from cte
where number <= 100) — CTE works for 100
select t.ID, t.ActiveFrom, t.ActiveTo, DATEADD(month,number,t.ActiveFrom) ActiveMonth
FROM #T t, cte c
WHERE c.number Between 0 And DATEDIFF(MONTH, t.ActiveFrom, t.ActiveTo)