SQL DATEDIFF 返回错误值

Question

我们想计算两个 DateTimeOffset 之间的差异，但是，SQL 返回了错误的值，我们做错了什么？

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 );

差异应该是 0 但它返回 1

Answer 1

如评论中所述，如果您转换它似乎有效。但不清楚为什么对我来说。

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