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.
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:00I have a table that hold time slots they go up in 30 mins starting form 06:00 am to 18:00pmExample06:0006:30...18:00Has 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 @TestSELECT '11:30:04' UNION ALLSELECT '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) BlockEndFROM @Test[/code]Peter LarssonHelsingborg, Sweden |
|
|
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 insteadselect datediff(second, dateadd(day, datediff(day, 0, datecol), 0), datecol) KH |
|
|
|
|
|
|
|