Better way to do this - 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)

Better way to do this

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-01-05 : 03:16:53

Hi guys,
I have 2 values of time.I want to insert into a table the values for every 15 minutes.
Example if user passes two time parameters say 08:00:00 & 12:00:00
I want to insert the values for every 15 minutes from 08:00:00 till 12:00:00 i.e 08:00:00,08:15:00,08:30:00,08:45:00....11:30:00,11:45:00,12:00:00.
I know how to do it using a while loop.But if the time diff is say 20 hrs then then the loop will be much longer.
I want to do it without using loops.Is there any other way around?
Please help.

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2008-01-05 : 04:03:03

Even for 00:00:00 to 23:45:00, there are only 96 records and completes under 1 second.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-01-05 : 04:36:30

I like to use a cte for these


Declare @d1 datetime,@d2 datetime

set @D1 = '12:00'
set @d2 = '13:49'
/* Note you can use full dates too ('01/01/2008 01:10' to '01/05/2008 3:30 pm' */
;with t1( d ) as
(
Select @d1
Union all
Select dateadd(minute,15,d)
from t1
where d <= dateadd(minute,-15,@D2)
)
select convert(varchar(60),d,108),d
 from t1
OPTION (MAXRECURSION 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources