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/2006' AS DATETIME) -- 2006 Not a Leap Year
SELECT CAST('06/31/2006' AS DATETIME) -- June only has 30 Days
SELECT CAST('13/31/2006' AS DATETIME) -- There are only 12 Months
SELECT CAST('01/01/1600' AS DATETIME) -- Year is Before 1753

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)

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 0
            THEN CAST([TranDate] AS DATETIME)
            ELSE CAST(NULL AS DATETIME) END AS [TranDate]
FROM [dbo].[CustomerTransactions]