Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
OMG! I wish they would just work the same. –  Simon Hughes Jul 10 at 10:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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