SQL Server - Date Time accuracy

This does not work as expected:

SET @day1159pm = DATEADD(millisecond,-1,DATEADD(day,1,@dayMidnight))

If @dayMidnight was 1/1/01 then @day1159pm will equal 2/1/01. It seems that it doesn’t keep DATETIME with enough precision to subtract 1 millisecond. Use this instead:

SET @day1159pm = DATEADD(millisecond,-1,DATEADD(day,1,@dayMidnight))

---

The reason for this is provided in the Transact-SQL reference:

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

Example	Rounded example
01/01/98 23:59:59.999	1998-01-02 00:00:00.000
01/01/98 23:59:59.995, 01/01/98 23:59:59.996, 01/01/98 23:59:59.997, or 01/01/98 23:59:59.998	1998-01-01 23:59:59.997
01/01/98 23:59:59.992, 01/01/98 23:59:59.993, 01/01/98 23:59:59.994	1998-01-01 23:59:59.993
01/01/98 23:59:59.990 or 01/01/98 23:59:59.991	1998-01-01 23:59:59.990