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, 4295🔥, 0💬
Popular Posts:
Is SQL Server Transact-SQL case sensitive? No. Transact-SQL is not case sensitive. Like the standard...
How REAL and FLOAT Literal Values Are Rounded in SQL Server Transact-SQL? By definition, FLOAT(n) sh...
Where to find answers to frequently asked questions on Downloading and Installing SQL Server 2005 Ex...
How To Turn on mysql Extension on the PHP Engine in MySQL? The "mysql" API extension is provided as ...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...