SQL Datetime comparison gotcha
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 Results in the following: Equals does not match Cast matches The comparison (=) operator does not perform th...