MS SQL Server DATETIME type

This post is from sometime in 2012:

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