January 12, 2011 at 1:49 am
hi,can any one help me in populating time dimensions in sql from 2000 to 2030 for every 15 min.now i need to create time dimensions from 2000 jan 01 to 2030 dec 31 by taking 15 min interval,for every day 96 rows will come
January 12, 2011 at 2:37 am
Here is one way of doing it. Maybe there is also a nice way of doing it using recursive CTE:-)
create table TimeTable (dt datetime)
insert into TimeTable (dt) values ('20000101')
declare @Interval int
set @Interval = 15
--In the loop I muliply the value of @interval each time, and then I use it
--to add it to the value of dt column of all records in the table.
while not exists (select * from TimeTable where dt >= '20291231 23:45:00')
begin
insert into TimeTable (dt)
select DATEADD(mi,@Interval,dt)
from TimeTable
where DATEADD(mi,@Interval,dt) < '20300101'
select @Interval = @Interval * 2
end
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 12, 2011 at 2:58 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply