We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL - Rounding datetime to the nearest hour

Medium Priority
979 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
dateadd(second, - datepart(second, ClockTime),  dateadd(minute, - datepart(minute, ClockTime), ClockTime) )
CERTIFIED EXPERT

Commented:

Author

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

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

Open in new window



REPLACE GETDATE() HERE WITH YOUR DATETIME.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
further if you only want the time part:-
SELECT     RIGHT(DATEADD(Hour, DATEDIFF(Hour, 0, '08:12:34'), 0), 7)

Open in new window

Author

Commented:
Thanks
another method for getting only time:-

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

Open in new window

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.