The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

SQL Errors and Solutions,The conversion of a varchar data type to a datetime data type resulted in an out-of-range value,SQL,SQL tips

SQL Server Error Message:

 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

This error occurs when the varchar value does not form a valid date.

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 (all based on US date format MM/DD/YYYY)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. For example, in United

To illustrate, if the date format expected by SQL Server is in the MM-DD-YYYY (US date format) format, the following statement will generate the error:

SELECT CAST('31-01-2006' AS DATETIME)

Solution/Workaround:

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].[Orders]
WHERE ISDATE([OrderDate]) = 1

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([OrderDate] AS DATETIME) AS [Order Date]
FROM [dbo].[Orders]

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([OrderDate]) WHEN 0
THEN CAST([OrderDate] AS DATETIME)
ELSE CAST(NULL AS DATETIME) END AS [Order Date]
FROM [dbo].[Orders]