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
- 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?