8

I discovered a problem when inserting data in our database. My insert statement was checking for the existence of data in the WHERE clause to prevent duplicate data being inserted. None was detected, and the INSERT happend. However, the unique constraint rejected the data as it already existed in the database.

The problem was the data to be inserted was DATETIMEOFFSET(2) and the database field being inserted into was DATETIME.

To show you want I'm talking about, run the following:

DECLARE @dt  DATETIME          = '2014-07-07 09:49:33.000';
DECLARE @dto DATETIMEOFFSET(2) = '2014-07-07 09:49:33.00 +07:00';

PRINT CASE WHEN @dt = @dto THEN 'Equals matches'
           ELSE 'Equals does not match'
      END

PRINT CASE WHEN @dt = CAST(@dto AS DATETIME) THEN 'Cast matches'
           ELSE 'Cast does not match'
      END

It prints:

  • Equals does not match
  • Cast matches

The comparison (=) operator does not perform the same way the implicit cast does if you insert the data. The cast/convert operator actually throws away the offset! Madness.

Why does the comparison operator work differently to the implicit cast that happends during an INSERT?

2 Answers 2

10

Looks like the opposit is true: the implicit conversion takes the offset into the equasion, but the cast/convert functions do not.

DECLARE @dt  DATETIME          = '2014-07-07 02:49:33.000';
DECLARE @dto DATETIMEOFFSET(2) = '2014-07-07 09:49:33.000 +07:00';

PRINT CASE WHEN @dt = @dto THEN 'Equals matches'
           ELSE 'Equals does not match'
      END

PRINT CASE WHEN @dt = CAST(@dto AS DATETIME) THEN 'Cast matches'
           ELSE 'Cast does not match'
      END

PRINT CASE WHEN @dt = CONVERT(DATETIME,@dto) THEN 'Convert matches'
           ELSE 'Convert does not match'
      END    

Comparing this (deducted 7 hours from @dt) results in:

  • Equals matches
  • Cast does not match
  • Convert does not match

Did some more investigation, and stumbled on this article.

"When you convert from datetime2 or datetimeoffset to date, there is no rounding and the date part is extracted explicitly. For any implicit conversion from datetimeoffset to date, time, datetime2, datetime, or smalldatetime, conversion is based on the local date and time value."

So when you want to treat '2014-07-07 09:49:33.000' and '2014-07-07 09:49:33.000 +07:00' as equal, your only option is to make an explicit conversion via cast or convert. Since implicit conversions would only work when your server's timezone offset happens to be the same as the offset specified in @dto.

0
2

Well, the comparison and CAST work differently.

Comparison uses type precedence to determine how to compare different types. DATETIMEOFFSET is on 4th place, DATETIME and DATETIME2 on 6th and 5th place respectively. So the DATETIME argument gets converted (using CAST, see below) to DATETIMEOFFSET first.

Converting to DATETIMEOFFSET can be achieved in three possible ways (maybe more?):

  • CAST which adds "+00:00" timezone (UTC)
  • CONVERT which adds "+00:00" timezone (UTC)
  • AT TIMEZONE which adds whichever timezone you supply as the right hand side argument

Sadly, I have never found any documentation on the behaviour of neither CAST nor CONVERT when "upscaling" to DATETIMEOFFSET. So this behaviour may differ between machines and versions.

On the other hand, converting to DATETIME chops off the "offset" part.

So, in your case:

@dt = @dto
  -- @dt gets converted to DATETIMEOFFSET(2)
  -- you are comparing:
  -- '2014-07-07 09:49:33.000 +00:00' = '2014-07-07 09:49:33.00 +07:00'

@dt = CAST(@dto AS DATETIME)
  -- botha are DATETIME, but @dto gets "downscaled" to DATETIME
  -- you are comparing:
  -- '2014-07-07 09:49:33.000' = '2014-07-07 09:49:33.00'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.