SQL Server Performance Forum – Threads Archive
Date Conversion/ Rounding
Is there an easier way of converting/rounding a date to the nearest hour. For example 1. 2006-04-06 14:20:40.530 should be rounded off to 2006-04-06 14:00:00.0002. 2006-04-06 03:33:40.530 should be rounded off to 2006-04-06 03:00:00.000 ( this works though 3:33 nearest would be 4:00 it needs to be rounded off to 3:00) I can achieve the above conversion by Dateadd/Date diff as shown below Set timesampled=dateadd(minute,-datepart(minute,timesampled),timesampled)
Set timesampled=dateadd(second,-datepart(second,timesampled),timesampled)
Set timesampled=dateadd(millisecond,-datepart(millisecond,timesampled),timesampled) But i want to know if a CONVERT or any other direct function would do this as the above way is time and resource consuming. Thanks
I could think of one another way which is as shown below Dateadd(hh,datepart(hh,getdate()),convert(Datetime, convert(varchar,getdate(),101))) Can any one think of a better solution than this?
I think your one is a godd solution that I can think off —————————————-
http://spaces.msn.com/members/dineshasanka
Raj1979, your solution is basically remove the minute, seconds, millisecs etc from the datetime.
see here selectdateadd(hour, datediff(hour, 0, dt), 0) as [remove minute, seconds etc],
dateadd(hour, round(datediff(minute, 0, dt) / 60.0,0), 0) as [Round to nearest hour]
from
(
selectconvert(datetime, ‘2006-04-05 06:03:34’) as dtunion all
selectconvert(datetime, ‘2006-04-05 06:57:34’) as dt
)d ——
> KH <
Thanks Khtan, i think your solution is better than what I had.
]]>