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 |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2009-10-05 : 11:48:56
|
| I'm looking for a good way to change a SmallDateTime to a 5 minute interval. e.g. 12:07PM = 12:05PM, 12:22PM = 12:20PM, etc. Anybody have some code to transform that?TIA,Ken |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-10-05 : 12:44:16
|
Function F_START_OF_05_MIN in the code in the link below will do what you want.Start of Time Period Functions:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755This code show how to do it "in-line" in a query:select My5Min = dateadd(mi,(datepart(mi,a.MyDateTime)/5)*5,dateadd(hh,datediff(hh,0,a.MyDateTime),0))from MyTable a CODO ERGO SUM |
 |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2009-10-05 : 12:53:03
|
| Exactly what I was looking for.Thanks! |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2009-10-05 : 13:15:39
|
| Sorry, one more ?. Why is the F_Start_Of_X_Min function non-deterministic? I would like to use this function within a Persisted Computed Column |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-10-05 : 14:05:08
|
| Why use a function in the computed column when you could just use the code from the function for the computed column definition?CODO ERGO SUM |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2009-10-06 : 09:09:52
|
| I wanted to have the flexibility to send a different parameter (minute interval) to the function, instead of hard coding each column. I thought it's non-deterministic due to the passing of the interval parameter, but then I tried it with the 5 minute function that only passes the date and it still was non-deterministic. Do you know why? |
|
|
|
|
|
|
|
|
Change SmallDateTime to 5 minute intervals