Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Out-of-Range Errors with Date and Time Literals

By: FYIcenter.com

(Continued from previous topic...)

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

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.

(Continued on next topic...)

1. What Is a Constant or Literal?
2. How To Write Character String Constants or Literals?
3. What Is a Collation?
4. How To Specify the Collation for a Character Data Type?
5. What Happens If Strings Are Casted into Wrong Code Pages?
6. How To Find Out What Is the Default Collation in a Database?
7. How Fixed Length Strings Are Truncated and Padded?
8. How To Enter Unicode Character String Literals?
9. How To Enter Binary String Literals?
10. How To Enter Date and Time Literals?
11. Why I Can Not Enter 0.001 Second in Date and Time Literals?
12. What Happens If Date-Only Values Are Provided as Date and Time Literals?
13. What Happens If Time-Only Values Are Provided as Date and Time Literals?
14. What Are Out-of-Range Errors with Date and Time Literals?
15. What Happens If an Integer Is Too Big for INT Date Type?
16. How Extra Digits Are Handled with NUMERIC Data Type Literals?
17. How REAL and FLOAT Literal Values Are Rounded?
18. What Are the Underflow and Overflow Behaviors on FLOAT Literals?