Rounding hour numbers doing daylight saving time

Question

Can anyone show how I do something as simple as round to the nearest integer?

select round((24+24+24)/24,0)    result: 3
select round((24+24+23)/24,0)    result: 2
select round((24+24+25)/24,0)    result: 3

Is there something I can do so all three examples will give me the result 3?

Thank you in advance

What kind of database is this? – Joel Coehoorn Mar 28 '22 at 19:03 — Joel Coehoorn, Mar 28 '22 at 19:03
@Blab MS produce multiple database engines. – Dale K Mar 28 '22 at 19:09 — Dale K, Mar 28 '22 at 19:09

Joel Coehoorn · Answer 1 · 2022-03-28T20:20:23.333

4

The problem is integer division. All of the values are integers, so the 2.9whatever is truncated to just 2 before you even start rounding.

This works just fine by including a floating point value with the division operation:

select round((24+24+24)/24.0,0)
select round((24+24+23)/24.0,0)
select round((24+24+25)/24.0,0)

The other option is always just throw in a bonus hour. Then you don't even need to round:

select (24+24+24+1)/24
select (24+24+23+1)/24
select (24+24+25+1)/24

edited Mar 28 '22 at 20:20

answered Mar 28 '22 at 19:06

Joel Coehoorn

1

I see now, I need to cast the hours as decimal, and then it'll work for me. thank you Joel. I see your latest edit with the 24.0. That's clever, thanks. – Blab Mar 28 '22 at 19:10

1 Answers1