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, 3648🔥, 0💬
Popular Posts:
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...
Where to find Oracle database server tutorials? Here is a collection of tutorials, tips and FAQs for...
How To Break Query Output into Pages in MySQL? If you have a query that returns hundreds of rows, an...
How To Generate CREATE TABLE Script on an Existing Table in SQL Server? If you want to know how an e...
How To Convert Numeric Expression Data Types using the CAST() Function in SQL Server Transact-SQL? I...