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, 3526🔥, 0💬
Popular Posts:
What Is "mysqld" in MySQL? "mysqld" is MySQL server daemon program which runs quietly in background ...
How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...
What are DDL (Data Definition Language) statements for tables in SQL Server? DDL (Data Definition La...
What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS) in SQL Server? Process sqlservr.exe is the...
What Happens to Your Transactions When ERROR 1213 Occurred in MySQL? If your transaction receives th...