INTERVIEW QUESTIONS DATABASE MS SQL SERVER DETAILS

Question: What Are Out-of-Range Errors with Date and Time Literals?

Answer: When you enter data and time literals, you may get out-of-range errors due to two common mistakes:

* The date value is a valid calendar date, but it is not in the range covered by DATETIME data type: from January 1, 1753, to December 31, 9999.
* The date value is not a valid calendar date, for example: 30-Feb-2007.
* The time value does not represent a valid time, for example: 24:55:07.233.

The tutorial exercise below shows you some data and time out-of-range errors:

-- Invalid date
DECLARE @x DATETIME;
SET @x = '30-Feb-2007 22:55:07.233';
GO
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.

-- Date below the DATETIME limits
DECLARE @x DATETIME;
SET @x = '19-May-1752 22:55:07.233';
GO
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.

-- Invalid time
DECLARE @x DATETIME;
SET @x = '19-May-2007 24:55:07.233';
GO
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.

When you enter data and time literals, you may get out-of-range errors due to two common mistakes:

* The date value is a valid calendar date, but it is not in the range covered by DATETIME data type: from January 1, 1753, to December 31, 9999.
* The date value is not a valid calendar date, for example: 30-Feb-2007.
* The time value does not represent a valid time, for example: 24:55:07.233.

The tutorial exercise below shows you some data and time out-of-range errors:

-- Invalid date
DECLARE @x DATETIME;
SET @x = '30-Feb-2007 22:55:07.233';
GO
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.

-- Date below the DATETIME limits
DECLARE @x DATETIME;
SET @x = '19-May-1752 22:55:07.233';
GO
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value.

-- Invalid time
DECLARE @x DATETIME;
SET @x = '19-May-2007 24:55:07.233';
GO
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value. 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