Creating an ID-Variable for certain time-intervals

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

General SQL Server Forums

New to SQL Server Programming

Creating an ID-Variable for certain time-intervals

Author

Topic

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2009-03-31 : 14:38:28

Hello,

I have a column, which contains a timestamp looking like this:

2008-12-08 08:31:09.600

Now I would like to apply a variable to each 2.5 minute time interval between 08:50:00 until 09:00:00 (afterwards also to other time-frames) eg:

08:50:00 - 08:52:29 = 1
08:52:30 - 08:54:59 = 2
etc

Anybody any idea if this is possible?

Thanks and regards,

Mike

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 14:42:42

Have a loop starting from your start-time to end-time, increment it with 2.5 minutes using dateadd and do an ordered insert into a table having an identity column.

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2009-03-31 : 15:40:58

I am not quite sue how the code should look like, I am not that good in sql..?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 15:49:41

[code]CREATE FUNCTION [dbo].[fn_Timetest] (@fromtime datetime, @totime datetime)
RETURNS @QueryResults TABLE(
fromtime datetime,
totime datetime,
id int IDENTITY(1,1)
) AS
BEGIN

WHILE (@fromtime <= @totime)
BEGIN
INSERT INTO @QueryResults(fromtime,totime)
SELECT
@fromtime,dateadd(second,150,@fromtime)
SET @fromtime = dateadd(second,150,@fromtime)
END

RETURN
END
[/code]

[code]--
declare @from datetime
declare @to datetime
set @from =getdate()
set @to =getdate()+1
select * from fn_Timetest(@from,@to)[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 16:13:41

If adding 2.5 minutes doesn't work, use 150 seconds instead.

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 16:53:40

quote:
Originally posted by Peso

If adding 2.5 minutes doesn't work, use 150 seconds instead.

E 12°55'05.63"
N 56°04'39.26"

ah, 2.5 won't work. Just noticed that increment in dateadd takes only the int part. thanks.

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2009-04-01 : 15:43:58

Awesome thank you so much!

One more thing: how would you write a "WHERE"-Statement, which says the time in the "time"-column should be between 08:51:10 and 08:51:20?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources