INTERVIEW QUESTIONS DATABASE MS SQL SERVER DETAILS

Question: Why I Can Not Enter 0.001 Second in Date and Time Literals in MS SQL Server?

Answer: If you enter milliseconds in data and time literals, they will be rounded up to 10/3 milliseconds 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 = '2007-05-19 22:55:07.233';
SELECT @x;
GO
2007-05-19 22:55:07.233

-- Rounded down to 0.000
DECLARE @x DATETIME;
SET @x = '2007-05-19 22:55:07.001';
SELECT @x;
GO
2007-05-19 22:55:07.000

-- Rounded up to 0.003
DECLARE @x DATETIME;
SET @x = '2007-05-19 22:55:07.002';
SELECT @x;
GO
2007-05-19 22:55:07.003

-- Rounded up to 0.007
DECLARE @x DATETIME;
SET @x = '2007-05-19 22:55:07.006';
SELECT @x;
GO
2007-05-19 22:55:07.007

If you enter milliseconds in data and time literals, they will be rounded up to 10/3 milliseconds 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 = '2007-05-19 22:55:07.233';
SELECT @x;
GO
2007-05-19 22:55:07.233

-- Rounded down to 0.000
DECLARE @x DATETIME;
SET @x = '2007-05-19 22:55:07.001';
SELECT @x;
GO
2007-05-19 22:55:07.000

-- Rounded up to 0.003
DECLARE @x DATETIME;
SET @x = '2007-05-19 22:55:07.002';
SELECT @x;
GO
2007-05-19 22:55:07.003

-- Rounded up to 0.007
DECLARE @x DATETIME;
SET @x = '2007-05-19 22:55:07.006';
SELECT @x;
GO
2007-05-19 22:55:07.007 Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question