What Are Out-of-Range Errors with Date and Time Literals?
Submitted by: AdministratorWhen 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.
Submitted by: Administrator
* 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.
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Select Some Specific Rows from a Table in MS SQL Server? |
☺ | What Happens If You Insert a Duplicate Key for the Primary Key Column in MS SQL Server? |
☺ | How To Execute the Cursor Queries with "OPEN" Statements? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | How To Configure ODBC DSN with Different Port Numbers? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |