Collections:
Entering 0.001 Second in DATETIME in SQL Server Transact-SQL
Why I Can Not Enter 0.001 Second in DATETIME values in SQL Server Transact-SQL?
✍: FYIcenter.com
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.
⇒ Date-Only DATETIME Values in SQL Server Transact-SQL
⇐ Casting Numeric Values to DATETIME in SQL Server Transact-SQL
2017-04-15, 3538🔥, 0💬
Popular Posts:
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...
How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...
Where to find SQL Server Transact-SQL language references? You can find SQL Server Transact-SQL lang...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
Where to find SQL Server database server tutorials? Here is a collection of tutorials, tips and FAQs...