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
 Old Forums
 CLOSED - General SQL Server
 Difficult Query

Author  Topic 

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-09-06 : 10:26:25





I want to find out how much time was between the time given and the time between every half an hour time slots.
For example if i have a time of
11:30:04 this would fall into the time slot of 11:30 - 12:00 and the time used within that time slot was 4 seconds.

Another example would be a give time of 08:52:04 this would fall into the time slot of 08:30 - 09:00 and the time used within that time slot was 22 mins 4 seconds.



Now things get a bit tricky what if my 22 mins 4 seconds span over 2 time slots as could any time..
Example start time was 08:20 End time was 08:42.

So the first 10 mins was in the time slot of 08:00 to 08:30
and the next 12 mins was in the time slot of 08:30 to 09:00


I have a table that hold time slots they go up in 30 mins starting form 06:00 am to 18:00pm

Example
06:00
06:30
.
.
.
18:00

Has anyone any ideas now how best to approach this..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 11:00:26
[code]DECLARE @Test TABLE (dt DATETIME)

INSERT @Test
SELECT '11:30:04' UNION ALL
SELECT '08:52:04'

SELECT CONVERT(VARCHAR, dt, 108) OriginalTime,
CONVERT(VARCHAR, DATEADD(minute, 30 * (DATEDIFF(second, 0, dt) / 1800), 0), 108) BlockStart,
DATEDIFF(second, 0, dt) % 1800 SecondsUsed,
CONVERT(VARCHAR, DATEADD(minute, 30 + 30 * (DATEDIFF(second, 0, dt) / 1800), 0), 108) BlockEnd
FROM @Test[/code]
Peter Larsson
Helsingborg, Sweden
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-06 : 11:15:33
Peter,

DATEDIFF(second, 0, YourDataTimeColumn)
this will cause error
"Difference of two datetime columns caused overflow at runtime."

compare with midnight 00:00:00 instead
select datediff(second, dateadd(day, datediff(day, 0, datecol), 0), datecol)




KH

Go to Top of Page
   

- Advertisement -