January 12, 2011 at 1:46 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.it has to done through ssis(sql server integration services)but not through stored procedures........plz help me if any one know
January 12, 2011 at 2:10 am
Here you go...
Not the most effecient solution, but it will do
set dateformat ymd
declare @start datetime
declare @increment float
declare @end datetime
set @start=(select cast('2000/01/01' as datetime))
set @end =(select cast('2031/01/01' as datetime))
if exists(select * from sysobjects where name ='Time')
drop table [Time]
Create table [Time]([Date] datetime not null)
while @start <= @end
begin
insert into [Time]
select @start
set @start=@start +(cast(1 as float)/24/4)
end
select * from [time]
January 12, 2011 at 3:17 am
thank you very much..........
still i need to refine like this........here we r printing year,mth,day in a single column..can u tell how to put that yr,mth,day in a seperate columns..
can u explain..........@start +(cast(1 as float)/24/4........i did not understand 1 as float
January 12, 2011 at 3:25 am
thank you very much..........
still i need to refine like this........here we r printing year,mth,day in a single column..can u tell how to put that yr,mth,day in a seperate columns..
can u explain..........@start +(cast(1 as float)/24/4........i did not understand 1 as float
Pleasure,
There are sql functions which can strip date parts from a date. For your first question,
Select Year([date]),month([Date]), day([Date])
from [Time]
Have a look at BOL for dateparts
secound question,
By adding a value 1 to a date you get the next day value. So in theory 1=1 day. Your reqiurement needed to be incremented by 15min.
So 1/12, will give us an hour value further /4 will give us quarter of an hour.
the cast ( as float) forces SQL not to look at it as an integer, because we are adding decimals.
hope this makes sense.
cheers,
Shanu
January 12, 2011 at 10:23 pm
thank you .......
can we do the same thing through ssis in sql server..........
if possible plz tell me..........
waiting for ur reply
January 12, 2011 at 10:43 pm
Yes you can,
But give it a shot and post for any assistance reqiured.
January 13, 2011 at 3:14 am
can u tell me in detail .......how to do that one in ssis......
January 13, 2011 at 3:17 am
gupta.sreekanth (1/13/2011)
can u tell me in detail .......how to do that one in ssis......
Sorry if this sounds harsh, but shouldn't you at least try it yourself?
All the people on this forum are unpaid volunteers. You cannot expect them to write free code for you all the time. We are all glad to help in the case you are stuck, but you have to do at least some effort yourself.
If you don't want to, hire a consultant to do it for you.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply