NerishaB
asked on
SQL - Rounding datetime to the nearest hour
Hi,
How would I round myu field "ClockTime" to the nearest hour?
So if I had 08:12:34, it must be rounded to 08:00:00
How would I round myu field "ClockTime" to the nearest hour?
So if I had 08:12:34, it must be rounded to 08:00:00
dateadd(second, - datepart(second, ClockTime), dateadd(minute, - datepart(minute, ClockTime), ClockTime) )
ASKER
is there a way to do this without creating a function?
YOU MAY TRY:-
REPLACE GETDATE() HERE WITH YOUR DATETIME.
SELECT DATEADD(Hour, DATEDIFF(Hour, 0, GETDATE()), 0)
REPLACE GETDATE() HERE WITH YOUR DATETIME.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
further if you only want the time part:-
SELECT RIGHT(DATEADD(Hour, DATEDIFF(Hour, 0, '08:12:34'), 0), 7)
ASKER
Thanks
another method for getting only time:-
select CONVERT(varchar(8),DATEADD(Hour, DATEDIFF(Hour, 0, '08:12:34'), 0),108)