MsSQL convert string to DateTime

Question

Why in MsSQL some milliseconds are rounded to next? For example:

SELECT CONVERT(datetime, '2006-04-25 15:50:59.996', 120)

Produces

2006-04-25 15:50:59.997

Answer 1

In MS SQL server datetime values has certain accuracy.

They rounded to increments of .000, .003, or .007 seconds.

If you need more precision (and you're running at least SQL Server 2008) - use datetime2 type, it has accuracy of 100 nanoseconds.