Reputation: 155
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.
Upvotes: 1
Views: 229
Reputation: 1271191
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.
Upvotes: 3