Build date time table 1

[roody91]

Hello,

I am trying to figure out a way to build a table of each hour of every day from 1/1/2003 through 12/31/2010. The table needs only a single datetime column. It will be used to develop a shared datetime dimension in analysis services that other tables with "real" datetimes will be joined.

The format of this datetime should be something like
2003/01/01 01:00:00
2003/01/01 02:00:00
2003/01/01 03:00:00
:
:
2010/12/31 21:00:00
2010/12/31 22:00:00
2010/12/31 23:00:00

I haven't a clue how to seed an initial value and then just keep adding an hour until the max date is reached.

Any help is appreciated.

Thanks,

Frank B.

 

[JohnDTampaBay]

How does this work:

DECLARE @TheDateTime datetime

SET @TheDateTime = '2003/01/01 01:00:00'

WHILE @TheDateTime <= '2010/12/31 23:00:00' BEGIN
  INSERT YourTable VALUES @TheDateTime

  SET @TheDateTime = DateAdd(hour, 1, @TheDateTime)
END

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[royjimenez]

look up dateadd function.

Regards

 

[roody91]

Angel, your name truly fits. Thank you so much! The only change I had to make was putting the (@TheDateTime) in ()

INSERT YourTable VALUES (@TheDateTime)

Have a wonderful weekend. Thanks again.

Frank B.