SQL Server Error Message - Msg 296¶

Error Message¶

Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Causes¶

This error occurs when trying to convert a date value into a SMALLDATETIME 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, consider the following SELECT statements that converts date values in MM/DD/YYYY format into SMALLDATETIME:

SELECT CAST('02/29/2006' AS SMALLDATETIME) -- 2006 Not a Leap Year
SELECT CAST('06/31/2006' AS SMALLDATETIME) -- June only has 30 Days
SELECT CAST('13/31/2006' AS SMALLDATETIME) -- There are only 13 Months
SELECT CAST('01/01/1899' AS SMALLDATETIME) -- Year is Before 1900

Executing these SELECT statements will generate the following error message:

Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime 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 SMALLDATETIME)

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