Imagine the following two statements run in SQL Server 2005+:
INSERT INTO tbl (mydate) VALUES ('2000-01-01 00:00:00.001');
UPDATE tbl SET mydate = null WHERE mydate = '2000-01-01 00:00:00.001';
It looks so simple, but the UPDATE statement will update 0 (ZERO) rows.
You might ask why. And the reason is the strange persicion for DATETIME column of SQL Server
http://msdn.microsoft.com/en-us/library/ms187819(v=sql.90)
...
So why does SQL Server round the value in first statement, but fails to do so in the second ?
Workarounds:
- If you are lucky enough to support only SQL Server 2008+, you might switch to DATETIME2
- Don't use DATETIME at WHERE clauses
- Round the value at application stage