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, 3522🔥, 0💬
Popular Posts:
How to download and install Microsoft .NET Framework Version 2.0 in SQL Server? .NET Framework Versi...
How To Drop an Index in Oracle? If you don't need an existing index any more, you should delete it w...
How To Use GO Command in "sqlcmd" in SQL Server? "sqlcmd" is a command line client application to ru...
How To Assign Debug Privileges to a User in Oracle? In order to run SQL Developer in debug mode, the...
How To Update Multiple Rows with One UPDATE Statement in SQL Server? If the WHERE clause in an UPDAT...