Home >> FAQs/Tutorials >> SQL Server FAQ

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

By: FYIcenter.com

(Continued from previous topic...)

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...)

  1. What Is a Constant or Literal?
  2. How To Write Character String Constants or Literals?
  3. What Is a Collation?
  4. How To Specify the Collation for a Character Data Type?
  5. What Happens If Strings Are Casted into Wrong Code Pages?
  6. How To Find Out What Is the Default Collation in a Database?
  7. How Fixed Length Strings Are Truncated and Padded?
  8. How To Enter Unicode Character String Literals?
  9. How To Enter Binary String Literals?
  10. How To Enter Date and Time Literals?
  11. Why I Can Not Enter 0.001 Second in Date and Time Literals?
  12. What Happens If Date-Only Values Are Provided as Date and Time Literals?
  13. What Happens If Time-Only Values Are Provided as Date and Time Literals?
  14. What Are Out-of-Range Errors with Date and Time Literals?
  15. What Happens If an Integer Is Too Big for INT Date Type?
  16. How Extra Digits Are Handled with NUMERIC Data Type Literals?
  17. How REAL and FLOAT Literal Values Are Rounded?
  18. What Are the Underflow and Overflow Behaviors on FLOAT Literals?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...