SQL - Transform GETDATE() into the exactly hour

Question

I've this:

SELECT GETDATE()

Which gives me :

2017-06-12 16:51:50.410

How can I convert this in order to get:

2017-06-12 16:00:00.000

I've some processes and I'm registering the date and hour of the job but I only want the exactly hour.

How can I do this?

Thanks

score 4 · Accepted Answer · answered Jun 12 '17 at 15:55

To get getdate() truncated to hour:

select dateadd(hour, datediff(hour, 0, getdate() ), 0)

This adds the number of hours since 1900-01-01 to the date 1900-01-01.

You can also swap hour for other levels of truncation: year, quarter, month, day, minute, second, et cetera.

GlennFriesen · Answer 2 · 2017-06-12T20:18:57.250

-1

I think the most human-readable solution is:

SELECT convert(datetime,FORMAT(GETDATE(),'yyyy-MM-dd HH:00:00.000'))

edited Jun 12 '17 at 20:18

answered Jun 12 '17 at 17:27

GlennFriesen

hh is a 12 hour clock, so 13:00 would be 1AM – John Cappelletti Jun 12 '17 at 17:36
thanks John. updated to from hh to HH for the 24 hour clock. – GlennFriesen Jun 12 '17 at 20:18
1

No worries. Just didn't want OP to get unexpected results. Moot point. Looks like we both got downvoted. Format() is a valid solution if used in a responsible manner. Many folks don't see it like that – John Cappelletti Jun 12 '17 at 21:37

2 Answers2