Another little data manipulation I've had to do recently is to split a heap of sql server datetime values into 15 minute segments (eg: 10:00, 10:15, 10:30 etc). Although there's no inherit function that I know about in sql server that achieves this, there's a nifty little trick using the modulus operator (%) that can do this.
select
case
when datepart(hour, DateTimeval) < 12 then 'AM'
else 'PM'
end as Meridiem,
Datepart(hour, DateTimeval) as Hour,
case
when (datepart(minute, DateTimeval) % 15 ) < (15 / 2) then datepart(minute, DateTimeval) - (datepart(minute, DateTimeval) % 15)
else (datepart(minute, DateTimeval) + (15 - (datepart(minute, DateTimeval) % 15))) % 60
end as QuarterHour
from
MyTable
Where DateTimeval is the column that holds the... date time value.
Firstly the < (15/2) part determines the range of the midpoints (ie: the midpoint 15 ranges from 7.5 to 22.5). Next it gets the minutes part from the value (eg: 10, 49, 15, etc), then does % 15. This will give the remainders (10, 04, 00). If the remainder is less that the range value (ie: < (15/2)), then by simply subtracting the remainder from the original value will give us the midpoint (eg: N/A, 49 - 4 = 45, 15 - 0 = 15).
If, however, the remainder is > (15 / 2), then we need to add the difference of (15 - remainder) to get the upper midpoint value (eg: 10 + (15 - 10) = 5, 45, 15).
And voila, the date time values are segmented into 15 minute chunks.
Of course you can substitute 15 with however many minutes you want to segment by (eg: 5, 10, 30 etc).
Performance has been Ok. I ran this over 200,000 records and it took about 3 seconds on a shared dev server. Not bad.
posted @ Tuesday, April 29, 2008 1:30 PM