This is an extrapolated question from the actual QoD, but I would like to know the reason (coz of knowledge gaining reason)
Why the 'mmm' part in the RESULT section giving a different value than I entered through the character string as shown below.
SET DATEFORMAT MDY
DECLARE @dmy datetime
SELECT @dmy = '12/31/99 12:13:12:012'
The accuracy of datetime is not to the millisecond, but to 1/300 of a second. So each second is divided into 300 slices of 3 1/3 milliseconds. And because display uses milliseconds, you see the values rounded to the millisecond: 0.000, 0.003, 0.007, 0.010, 0.013, 0.017, .....
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis