Populate TimeTable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Populate TimeTable

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-09-16 : 07:02:48

Hi

Im trying to populate an empty table from scratch with datetimesstamps
with an certain interval between

Like this:

DateTime
...
2009-10-01 00:10:00
2009-10-01 00:20:00
...
and so on

I want to produce new datetimes with 10 minutes for example
from date A to datetime B so i have an hole table with only this stamps.

How/where to go ?

Grateful
V

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-16 : 07:15:01

Give it a try by using a while loop from start time, to end time and increment it by 10 minutes everytime. To add minutes, you could use dateadd function.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-16 : 07:16:57

You almost have everything here
http://www.sqlbook.com/Data-Warehousing/Date-Dimension-SQL-script-18.aspx

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-09-16 : 07:17:43

Thanks sakets_2000

I will try that. I guess thats the only way. I first believe it could be created in a simple query

Sachin.Nand

2937 Posts

Posted - 2010-09-16 : 07:27:25

[code]
declare @interval int=10
declare @startdt datetime ='16-Sep-2010 01:00:00'
declare @enddt datetime ='16-Sep-2010 05:00:00'
;with cte
as
(
select @startdt as startdate
union all
select DATEADD(minute,10,cte.startdate) from cte where cte.startdate<@enddt

)

select * from cte
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-09-16 : 08:34:24

Thanks Idera:

When i change datetime periode
I got the message:
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
So the will limit the imaginations unfortunate for me :-)

Sachin.Nand

2937 Posts

Posted - 2010-09-16 : 09:15:18

Change the select statement to this


select * from cte
option(maxrecursion 0)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-09-16 : 09:54:45

No limits anymore for my imaginations

Thanks PBUH
This was very useful :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources