Date dimension?

[SQLnewcommer]

Hello,
I am trying to create a date dimension. I have no idea where to start but I just gave it a try and came up with the following code.

I am going to put this in a while loop .
and I have declared @x as an int.

insert into Date_source values
dateadd('qq',1,'12/31/1999')
set @x = x + 1

But I am getting the following error:

Line 10: Incorrect syntax near 'dateadd'.

Any suggestions?

 

[SQLnewcommer]

I meant dateadd('qq',@x,'12/31/1999')

 

[SQLDenis]

declare @x int
set @x = 1
while @x < 10
begin

insert into Date_source
select dateadd(qq,@x,'12/31/1999')

set @x = @x + 1
end

Untested since I don't have QA near me but it should work
The problem was with 'qq' should be qq

of course you can do this much faster take a look at the code below

-- Create out Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <=1000
BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1
END
GO


--********* THE ACTUAL INSERT STARTS HERE***********
insert into Date_source
SELECT DATEADD(qq,numberID,GETDATE())
FROM dbo.NumberPivot
WHERE NumberID < 10

Of course you would modify getdate() to the date that you would like to start at, full code is here (

http://sqlservercode.blogspot.com/2005/09/fast-date-ranges-without-loops-in-sql.html)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/