Finding months between given dates

Question

Possible Duplicate:
Months between two dates

I need to bring the result ,what are the months falls between two dates.

Eg. Suppose date is

Declare @FrDate datetime,@ToDate datetime
Set @FrDate ='2010-05-31 17:38:58.577' 
Set @ToDate ='2010-09-01 17:38:58.577'

need Output

Result
MAY
JUN
JUL
AUG
SEP

A date dimension table would make this problem simple... – Griffin Oct 03 '12 at 13:14 — Griffin, Oct 03 '12 at 13:14

Himanshu · Accepted Answer · 2012-10-03T13:00:34.187

Try this one:

DECLARE @FrDate  DATETIME,
        @ToDate    DATETIME;

Set @FrDate ='2010-05-31 17:38:58.577' 
Set @ToDate ='2010-09-01 17:38:58.577'

;WITH MONTHS (date)
AS
(
    SELECT @FrDate
    UNION ALL
    SELECT DATEADD(month,1,date)
    from months
    where date<=@ToDate
)
SELECT Datename(month,date) AS MONTH FROM MONTHS

Result:

MONTH
-----------------
May
June
July
August
September

(5 row(s) affected)

To get short month name in result you can use the following line instead:

SELECT LEFT(CONVERT(VARCHAR,date,100),3) AS MONTH FROM MONTHS

To get month name in UPPER case you can use UPPER function for that:

SELECT UPPER(LEFT(CONVERT(VARCHAR,date,100),3)) AS MONTH FROM MONTHS

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

Create a calendar table, then you can simply write this:

select distinct MonthName
from dbo.Calendar
where BaseDate between @StartDate and @EndDate

edited May 23 '17 at 12:18

Community

1
1

answered Oct 03 '12 at 13:00

Pondlife

15,992
6
37
51

score 0 · Answer 3 · answered Oct 03 '12 at 13:12

Another method

Declare @FrDate datetime,@ToDate datetime 
Set @FrDate ='2010-05-31 17:38:58.577'  
Set @ToDate ='2010-09-01 17:38:58.577' 

select 
    datename(month,dateadd(month,number-1,@FrDate)) as month_name
from 
    master..spt_values 
where 
    type='p'and number between 1 and datediff(month,@frdate,@todate)+1

Finding months between given dates

3 Answers3