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
NerishaBAsked:
Who is Participating?
 
mayank_joshiConnect With a Mentor Commented:
E.G:-
SELECT     DATEADD(Hour, DATEDIFF(Hour, 0, '08:12:34'), 0)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
dateadd(second, - datepart(second, ClockTime),  dateadd(minute, - datepart(minute, ClockTime), ClockTime) )
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
NerishaBAuthor Commented:
is there a way to do this without creating a function?
0
 
mayank_joshiCommented:
YOU MAY TRY:-

SELECT   DATEADD(Hour, DATEDIFF(Hour, 0, GETDATE()), 0)

Open in new window



REPLACE GETDATE() HERE WITH YOUR DATETIME.
0
 
mayank_joshiCommented:
further if you only want the time part:-
SELECT     RIGHT(DATEADD(Hour, DATEDIFF(Hour, 0, '08:12:34'), 0), 7)

Open in new window

0
 
NerishaBAuthor Commented:
Thanks
0
 
mayank_joshiCommented:
another method for getting only time:-

select CONVERT(varchar(8),DATEADD(Hour, DATEDIFF(Hour, 0, '08:12:34'), 0),108)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.