SQL Server DATEDIFF hour rounding

Question

Can anyone explain why

select DATEDIFF(HOUR, '2018-02-01 08:30:00', '2018-02-01 10:00:00')

is returning 2, and

select DATEDIFF(HOUR, '2018-02-01 08:00:00', '2018-02-01 9:30:00')

is returning 1?

I feel like I'm missing something obvious here, but I can't find my answer on MSDN..

I would expect them to both return 1 (round down to the nearest full hour), an example on how to achieve this would be appreciated.

Answer 1

As noted, it's because DATEDIFF uses boundaries (for hours, it is 09:00, 10:00, 11:00 etc).

If you want rounded down hours, then simply apply some integer division to a minute difference

 select DATEDIFF(minute, '2018-02-01 08:00:00', '2018-02-01 9:30:00') / 60

Answer 2

For hours it will ignore the Minutes, seconds etc... see this in seconds

select DATEDIFF(SECOND, '2018-02-01 08:30:00', '2018-02-01 10:00:00')
select DATEDIFF(SECOND, '2018-02-01 08:00:00', '2018-02-01 9:30:00')

OUTPUT

-----------
5400

(1 row affected)


-----------
5400

(1 row affected)

Get the difference in seconds and the convert to Hours.

Check this out. - Changed code

select DATEDIFF(SECOND, '2018-02-01 08:30:00', '2018-02-01 10:00:00') / 3600.0


---------------------------------------
1.500000

(1 row affected)


select DATEDIFF(SECOND, '2018-02-01 08:00:00', '2018-02-01 9:30:00') / 3600.0



---------------------------------------
1.500000

(1 row affected)