DATETIME has accuracy of 3 ticks - it does not support 999 ms. The
value is rounded to the nearest supported value.

Out of curiosity, what are the supported values? The behavior
reported above (991 rounded down, 997 left alone, 999 rounded up)
is still weird - it suggests that the supported values are 3 ticks
apart, but with a 1-tick jump somewhere in the 991-997 range.

DATETIME has accuracy of 3 ticks - it does not support 999 ms. The
value is rounded to the nearest supported value.

Out of curiosity, what are the supported values? The behavior
reported above (991 rounded down, 997 left alone, 999 rounded up)
is still weird - it suggests that the supported values are 3 ticks
apart, but with a 1-tick jump somewhere in the 991-997 range.

It's all in BOL.

"datetime values are rounded to increments of .000, .003, or .007
seconds"

See
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/9bd1cc5b-227b-4032-95d6-7581ddcc9924.htm
for example 'rounding' with data type datetime and smalldatetime.