|
|
SQL Server Error Messages - Msg 242
Error Message
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data
type resulted in an out-of-range datetime value.
Causes
This error occurs when trying to convert a string date value into a DATETIME data type but the date value contains an invalid date. The individual parts of the date value (day, month and year) are all numeric but together they don?t form a valid date.
|
To illustrate, the following SELECT statements will generate the error:
SELECT CAST('02/29/2025' AS DATETIME) -- 2025 not a leap year
Msg: 242, Line 1, State: 3, Level: 16
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('06/31/2025' AS DATETIME) -- June only has 30 days
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('13/31/2025' AS DATETIME) -- There are only 12 months
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('01/01/1600' AS DATETIME) -- Year is before 1753
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('01/01/2025 13:00:00AM' AS DATETIME) -- Hour contradicts with AM
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('01/01/2025 25:00:00' AS DATETIME) -- 25 is not a valid hour
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('01/01/2025 12:60:00' AS DATETIME) -- 60 is not a valid minute
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('01/01/2025 12:00:60' AS DATETIME) -- 60 is not a valid second
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Another way the error may be encountered is when the format of the date string does not conform to the format expected by SQL Server as set in the SET DATEFORMAT command. To illustrate, if the date format expected by SQL Server is in the MM-DD-YYYY format, the following statement will generate the error:
SELECT CAST('31-01-2006' AS DATETIME)
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Solution / Work Around
To avoid this error from happening, you can check first to determine if a certain date in a string format is valid using the ISDATE function. The ISDATE function determines if a certain expression is a valid date. So if you have a table where one of the columns contains date values but the column is defined as VARCHAR data type, you can do the following query to identify the invalid dates:
SELECT * FROM [dbo].[CustomerTransactions]
WHERE ISDATE([TranDate]) = 0
Once the invalid dates have been identified, you can have them fixed manually then you can use the CAST function to convert the date values into DATETIME data type:
SELECT CAST([TranDate] AS DATETIME) AS [TranDate]
FROM [dbo].[CustomerTransactions]
Another way to do this without having to update the table and simply return a NULL value for the invalid dates is to use a CASE condition:
SELECT CASE ISDATE([TranDate]) WHEN 1
THEN CAST([TranDate] AS DATETIME)
ELSE CAST(NULL AS DATETIME) END AS [TranDate]
FROM [dbo].[CustomerTransactions]
|