SQL Server FAQ - Entering 0.001 Second in Data and Time Literals

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

Why I Can Not Enter 0.001 Second in Date and Time Literals?

If you enter milliseconds in data and time literals, they will be rounded up to 10/3 milliseconds 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 = '2007-05-19 22:55:07.233';
SELECT @x;
GO
2007-05-19 22:55:07.233

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

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

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

(Continued on next topic...)