CODEWITHSUNDEEP
sqlsql-servert-sqldatetimedatetimeoffset
Osel Miko Dřevorubec
Osel Miko Dřevorubec

Reputation: 182

SQL DATEDIFF return wrong value

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

Upvotes: 3

Views: 2826

Answers (2)

paparazzo
paparazzo

Reputation: 45096

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

Upvotes: 1

Andomar
Andomar

Reputation: 238086

Very interesting! As Jeroen Mostert commented, it looks like the datetimeoffset values are converted to UTC:

select datediff(day, '2017-01-01 0:00 +1:00', '2017-01-01 1:00 +1:00') --> 1

Conversion to UTC would make the first value `2016-12-31 23:00', which is a day earlier.

But normal datetime values are not converted to UTC:

select datediff(day, '2017-01-01 0:00', '2017-01-01 1:00') --> 0

Living in UTC+1, it's surprising that adding +1:00 gives a different result. After all +1:00 is the default here.

Upvotes: 0

Related Questions