CODEWITHSUNDEEP
sqlsql-server
Nithesh Narayanan
Nithesh Narayanan

Reputation: 11775

Months between two dates

Is it possible to get month names between two dates in SQl

ie, 2011-05-01 And 2011-08-01 are the inputs I just want the output as

------------
Month
------------
May
June
July
August

If any body knows the query please share.

Upvotes: 40

Views: 108122

Answers (8)

Elan Hasson
Elan Hasson

Reputation: 1270

I've modified Jamiec's answer to output the Last day of the month as well.

declare @start DATE = '2014-05-01'
declare @end DATE = getdate()

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month, 1, date)
    from months
    where DATEADD(month, 1, date) < @end
)
select     [MonthName]    = DATENAME(mm, date),
           [MonthNumber]  = DATEPART(mm, date),  
           [LastDayOfMonth]  = DATEPART(dd, EOMONTH(date)),
           [MonthYear]    = DATEPART(yy, date)
from months

Which gives output:

MonthName   MonthNumber LastDayOfMonth  MonthYear
May         5           31              2014
June        6           30              2014
July        7           31              2014
August      8           31              2014
September   9           30              2014

Upvotes: 16

Dhruv Patel
Dhruv Patel

Reputation: 136

Create Database function like below

CREATE FUNCTION [dbo].[DateRange] 
(@Identifier CHAR(1),@StartDate DATETIME,@EndDate DATETIME)
RETURNS @SelectedRange TABLE(Dates DATE) AS
 BEGIN

   ;WITH cteRange (DateRange) AS (
   SELECT @StartDate
     UNION ALL
   SELECT
     CASE
         WHEN Upper(@Identifier) = 'H' THEN DATEADD(hh, 1, DateRange)
         WHEN Upper(@Identifier) = 'D' THEN DATEADD(dd, 1, DateRange)
         WHEN Upper(@Identifier) = 'W' THEN DATEADD(ww, 1, DateRange)
         WHEN Upper(@Identifier) = 'M' THEN DATEADD(mm, 1, DateRange)
         WHEN Upper(@Identifier) = 'Y' THEN DATEADD(yy, 1, DateRange)
     END
   FROM cteRange
   WHERE DateRange <=
    CASE
        WHEN Upper(@Identifier) = 'H' THEN DATEADD(hh, -1, @EndDate)
        WHEN Upper(@Identifier) = 'D' THEN DATEADD(dd, -1, @EndDate)
        WHEN Upper(@Identifier) = 'W' THEN DATEADD(ww, -1, @EndDate)
        WHEN Upper(@Identifier) = 'M' THEN DATEADD(mm, -1, @EndDate)
        WHEN Upper(@Identifier) = 'Y' THEN DATEADD(yy, -1, @EndDate)
    END)
 INSERT INTO @SelectedRange (Dates) SELECT DateRange FROM cteRange
  OPTION (MAXRECURSION 3660);
RETURN
END

Then using the function we can generate a range of dates 

SELECT * from dbo.DateRange('M','1953-01-01','2019-01-01')

If we want formatted output we can store the result in table variable like below example,

DECLARE @tblDateRange TABLE (AutoID INT IDENTITY(1,1),DateRange DATE)
INSERT INTO @tblDateRange SELECT * from dbo.DateRange('M','1953-01-01','2019-01-01')
SELECT 
LEFT(DATENAME(MONTH,DateRange),3) [MonthYearValue],YEAR(DateRange) AS [Year]
FROM @tblDateRange

Based on our need we can change 

OPTION (MAXRECURSION 3660)

Upvotes: 0

gotqn
gotqn

Reputation: 43666

Inspired by Jamiec's answer, but fixing issue with with from day bigger then to day:

declare @start DATE
declare @end DATE 

SELECT  @start='2011-05-19' , @end='2011-08-15' 

;with months (date)
AS
(
   SELECT DATEADD(DAY,1,EOMONTH(@start,-1))
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where DATEADD(month,1,date) < EOMONTH(@end)
)
select Datename(month,date)
from months

Upvotes: 2

Biju jose
Biju jose

Reputation: 273

Well, @bogdhan sahlean has given a nice set based solution, but restricts the values upto 2048 considering the datatype date and datetime2 which the range for year is 0001-01-01 to 9999-12-31, From MSDN

Date range 0001-01-01 through 9999-12-31

January 1,1 CE through December 31, 9999 CE

even though this is the extreme case but worth knowing. Since what if one day someone is trying to project months more than 170 years :)

Even the most upvoted answers are not fulfilling some edge cases (when start date >end date will not show the month of end date, also, the recursive query fails after 100 executions by default). And also using recursive cte for iteration which is performance hog when used massively.

Now , a better solution (IMHO) is to use a calendar table or a tally table to generate the months between two dates. IF one can't create a table , there is better alternative to use Itzik ben Gans cascading CTE for generating numbers table.(here) Which is faster,No logical,physical reads,No worktable NADA

Here is the code

DECLARE @start DATETIME2 = '00010101'
DECLARE @end DATETIME2 = '99991231'
;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
    ,Tally (n) AS (SELECT 0 UNION SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)
SELECT DATENAME(YEAR,DATEADD(MONTH,N,@start)) AS [Year Part], DATENAME(MONTH,DATEADD(MONTH,n,@start)) AS [Month Part]
FROM Tally where N between 0 and DATEDIFF(mm,@start,@end)  
ORDER BY n;

NB: I have added SELECT 0 to start the numbers from 0 th position

The performance shown in my PC is

Itzik Method

(119988 row(s) affected)

SQL Server Execution Times: CPU time = 187 ms, elapsed time = 706 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

One of the recursive solution given here which takes a time of

(119988 row(s) affected) Table 'Worktable'. Scan count 2, logical reads 719923, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 890 ms, elapsed time = 1069 ms.

The performance between tally table, calendar table and itzik number table may vary slightly but works like charm with all date range you supply.

Upvotes: 0

Vikas P
Vikas P

Reputation: 13

Try this:

declare 
       @sd date=getdate(),
       @ld date='2016-01-01'

select 
     Datename(month,dateadd(month,number,GETDATE())), 
     number
from master.dbo.spt_values 
where type='p' 
     and dateadd(month,number,GETDATE()) <= @ld

Upvotes: -1

Sumesh
Sumesh

Reputation: 31

    declare @start DATE = '2011-05-30'
    declare @end DATE = '2011-06-10' 
   ;with months (date)
    AS
    (
        SELECT @start
        UNION ALL
        SELECT DATEADD(month,1,date)
        from months
        where DATEADD(month,1,date)<= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@end)+1,0))
    )
    select Datename(month,date) from months

Upvotes: 1

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20110501'        
        ,@EndDate   = '20110801';


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

Results:

MonthName
------------------------------
May
June
July
August

(4 row(s) affected)

Upvotes: 54

Jamiec
Jamiec

Reputation: 136229

You can do this with a recursive CTE, by building up a table of dates, and getting the month name from each:

declare @start DATE = '2011-05-01'
declare @end DATE = '2011-08-01'

;with months (date)
AS
(
    SELECT @start
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where DATEADD(month,1,date)<=@end
)
select Datename(month,date) from months

Upvotes: 38

Related Questions