Possible Duplicate:
Months between two dates
I have date range as [start date] = '2012-09-01' and [finish date] = '2014-01-01'
Now, I need to display months between two dates..using a single select statement
Expected Output :
9
10
11
12
1
2
3
.
.
.
12
1
How can I do this?
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SELECT @StartDate = '20120901'
,@EndDate = '20140101';
;WITH MONTHS (date)
AS
(
SELECT @StartDate
UNION ALL
SELECT DATEADD(MONTH,1,date)
FROM MONTHS
WHERE DATEADD(MONTH,1,date)<=@EndDate
)
SELECT MONTH(date) AS MONTH FROM MONTHS
Result:
MONTH
-----------
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
1
(17 row(s) affected)
EDIT: As per your updated requirement you can achieve this using following query:
DECLARE @StartDate DATETIME,
@EndDate DATETIME;
SELECT @StartDate = '20120901'
,@EndDate = '20140101';
SELECT MONTH(DATEADD(MONTH, x.number, @StartDate)) AS Months
FROM master.dbo.spt_values x
WHERE x.type = 'P'
AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);
Result:
Months
-----------
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
1
(17 row(s) affected)
You can user table variables also:
DECLARE @startdt DATETIME, @enddt DATETIME
SELECT @startdt = '2012-09-01', @enddt = '2014-01-01'
DECLARE @Months TABLE (Months INT)
INSERT INTO @Months VALUES (MONTH(@startdt))
WHILE @startdt < @enddt
BEGIN
SET @startdt = DATEADD(MONTH,1,@startdt)
INSERT INTO @Months VALUES (MONTH(@startdt))
END
SELECT * FROM @Months
