Why I Can Not Enter 0.001 Second in DATETIME values in SQL Server Transact-SQL?

If you enter milliseconds in data and time values, they will be rounded up to 10/3 millisecond increments, because DATETIME data type uses 4 bytes to store the time of the day. A 4-byte integer can only give an accuracy of one three-hundredth second, or 3.33 milliseconds.

So if you enter a time with 0.001 second, it will be rounded to 0.000 second. The tutorial exercise below gives you some good examples of how milliseconds are rounded by the SQL Server.

-- No rounding
DECLARE @x DATETIME;
SET @x = '2017-05-19 22:55:07.233';
SELECT @x;
----------------------------
2017-05-19 22:55:07.233

-- Rounded down to 0.000
DECLARE @x DATETIME;
SET @x = '2017-05-19 22:55:07.001';
SELECT @x;
----------------------------
2017-05-19 22:55:07.000

-- Rounded up to 0.003
DECLARE @x DATETIME;
SET @x = '2017-05-19 22:55:07.002';
SELECT @x;
----------------------------
2017-05-19 22:55:07.003

-- Rounded up to 0.007
DECLARE @x DATETIME;
SET @x = '2017-05-19 22:55:07.006';
SELECT @x;
----------------------------
2017-05-19 22:55:07.007

If you want to store higher precision date and time values, you need to use DATETIME2 data type.

 

⇒Variables and Data Types in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials