How to generate a serie of date without create a new database object?

Question

I need to create a query which returns years and months from april 2013 to now like following:

2013  | 4
2013  | 5
2013  | 6
2013  | 7
2013  | 8
....  | .
....  | .
....  | .
2015  | 1
2015  | 2
2015  | 3
2015  | 4

It must works with SQLServer 2000, and all solution I found on SE are only SQLServer2005+ compatible.

Is there a way to do it with this (very) old SGBD?

If there is no solution I would consider to create a table of numbers but I don't find it really elegant...

If you're working with dates you should always have a calendar table. — Mark Sinkinson, Apr 23 '15 at 08:09
Have a look here for a calender table: http://stackoverflow.com/questions/5635594/how-to-create-a-calender-table-for-100-years-in-sql — NickyvV, Apr 23 '15 at 08:21

score 0 · Answer 1 · edited Apr 23 '15 at 09:36

0

declare @Startdate AS DateTime = Dateadd( year,-2,Getdate()) , 
        @EndDate DateTime = Getdate()
Declare @YearTable as Table ( CalYear int , calmonth int)

While @Startdate <= @EndDate 
BEGIN 
    Insert Into @YearTable values(YEAR(@Startdate),MONTH(@Startdate))
    set @Startdate = DateADD(Month,1,@Startdate)
END 

select * from @YearTable

edited Apr 23 '15 at 09:36

amcdermott

1,565
15
23

answered Apr 23 '15 at 08:51

Arun Gairola

884
4
14

How to generate a serie of date without create a new database object?

1 Answers1