SQL DATEDIFF return wrong value

Question

We want to calculate the difference between two DateTimeOffsets, however, SQL returns wrong value, what are we doing wrong?

DECLARE @timeInZone1 AS DATETIMEOFFSET
DECLARE @timeInZone2 AS DATETIMEOFFSET
SET @timeInZone1 = '2012-01-13 00:00:00 +1:00';
SET @timeInZone2 = '2012-01-13 23:00:00 +1:00';
SELECT DATEDIFF( day, @timeInZone1, @timeInZone2 );

The difference should be 0 but it returns 1

Answer 1

As stated in a comment if you cast it seems to work. But not clear why to me.

DECLARE @timeInZone1 AS DATETIMEOFFSET
DECLARE @timeInZone2 AS DATETIMEOFFSET
SET @timeInZone1 = '2012-01-13 00:00:00 +1:00';
SET @timeInZone2 = '2012-01-13 23:00:00 +1:00';
SELECT  @timeInZone1 as z1, @timeInZone2 as z2
      , cast(@timeInZone1 as datetime) z1d,  cast(@timeInZone2 as datetime) z2d 
      , DATEDIFF(day, @timeInZone1, @timeInZone2) as diff
      , DATEDIFF(day, cast(@timeInZone1 as datetime), cast(@timeInZone2 as datetime)) as diffdt;

z1                                 z2                                 z1d                     z2d                     diff        diffdt
---------------------------------- ---------------------------------- ----------------------- ----------------------- ----------- -----------
2012-01-13 00:00:00.0000000 +01:00 2012-01-13 23:00:00.0000000 +01:00 2012-01-13 00:00:00.000 2012-01-13 23:00:00.000 1           0