CODEWITHSUNDEEP
sqlexcelinteger
BrianJBaldwin
BrianJBaldwin

Reputation: 155

round sql integer to nearest hour

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions