round sql integer to nearest hour

Question

I have a column formatted as an integer (values ex. 2300, 0040, 2090) with values representing military time. What would be the easiest way to round these values to the nearest hour? So, if 2330, would round up to 2400, etc. I don't want the values converted to date/time.

I'm looking for help in either SQL or Excel. Thanks.

Answer 1

Simple arithmetic. Most SQL dialects support floor():

select floor((col+70) / 100) * 100

In Excel, you would use:

select floor((col+70) / 100, 1) * 100

You still have an issue with 0000 and 2400. If that is an issue, then you would need to use conditional logic or modulo arithmetic.