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 the same way the implicit cast does if you insert the data. The cast/convert operator actually throws away the offset! Madness.