• Messages 1-500
• Messages 501-1000
• Messages 1001-1500
• Messages 1501-2000
• Messages 2001-3000
• Messages 3001-4000
• Messages 4001-5000
• Messages 5001-6000
• Messages 6001-7000
• Messages 7001-7500
• Messages 7501-8000
• Messages 8001-8500
• Messages 8501-9000
• Messages 9001-9500
• Messages 9501-10000
• Messages 10001-10500
• Messages 10501-11000
• Messages 11001-11500
• Messages 11501-12000
• Messages 12001-13000
• Messages 13001-13500
• Messages 14001-14500

Server: Msg 295, Level 15, State 1,
Conversion failed when converting character string to 
smalldatetime data type.

This error usually occurs when a date and time value is being converted from a string representation to a SMALLDATETIME data type representation and SQL Server cannot perform the conversion due to an incorrectly formatted date/time value.

Here are a few samples of how the error can be encountered:

SELECT CAST('2011 01 01' AS SMALLDATETIME) –- Missing Date Separators

Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.

SELECT CAST('2011 Febraury 01' AS SMALLDATETIME) –- Misspelled Month

Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.

SELECT CAST('01 01 2011' AS SMALLDATETIME) –- Missing Date Separators

Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type. 

SELECT CAST('25 de Diciembre 2011' AS SMALLDATETIME) –- Date in Different Language

Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type. 

SELECT CAST('01//2011' AS SMALLDATETIME) –- Missing Day Part

Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type. 

There are other different ways of how this error can be encountered but what’s common among all of them is that the string value cannot be converted by SQL Server into a SMALLDATETIME data type.

A different error message will be generated if the date/time string value being converted to SMALLDATETIME is not between January 1, 1900 and June 6, 2079, as can be seen in the following script:

SELECT CAST('01/01/1899' AS SMALLDATETIME)

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

SELECT CAST('01/01/2100' AS SMALLDATETIME) 

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

Solution / Work Around:

To avoid this error, make sure that the character string containing the date/time value is a valid date/time that can be converted to SMALLDATETIME data type.

If the data is coming from another table where the column containing the date value is defined as VARCHAR and the data will be loaded to a table where the destination column is defined as SMALLDATETIME, the ISDATE() function can be used to determine whether an input expression is a valid date. The ISDATE() function returns 1 if the input expressions is a valid date; otherwise it returns 0.

Here’s an example of how to load data coming from one table into another where the column is defined as VARCHAR in the source table and defined as SMALLDATETIME in the destination table:

CREATE TABLE [dbo].[Transaction] (
    [TransactionName]       VARCHAR(50),
    [TransactionDate]       SMALLDATETIME,
    [Amount]                MONEY
)

CREATE TABLE [dbo].[tmpTransaction] (
    [TransactionName]       VARCHAR(50),
    [TransactionDate]       VARCHAR(20),
    [Amount]                MONEY
)

INSERT INTO [dbo].[tmpTransaction] ( [TransactionName], [TransactionDate], [Amount] )
VALUES ( 'ATM Deposit', '02/19/2011', 1000.00 )

INSERT INTO [dbo].[tmpTransaction] ( [TransactionName], [TransactionDate], [Amount] )
VALUES ( 'ATM Withdrawal', '02//2011', 250.00 )

INSERT INTO [dbo].[tmpTransaction] ( [TransactionName], [TransactionDate], [Amount] )
VALUES ( 'Cash Withdrawal', '01/01/2011', 5000.00 )

INSERT INTO [dbo].[Transaction] ( [TransactionName], [TransactionDate], [Amount] )
SELECT [TransactionName], [TransactionDate], [Amount]
FROM [dbo].[tmpTransaction]
WHERE ISDATE([TransactionDate]) = 1

(2 row(s) affected)

Alternatively, if all data needs to be loaded to the destination table, the invalid date values can be converted to NULL using the following:

INSERT INTO [dbo].[Transaction] ( [TransactionName], [TransactionDate], [Amount] )
SELECT [TransactionName],
       CASE WHEN ISDATE([TransactionDate]) = 1
            THEN [TransactionDate] ELSE NULL END,
       [Amount]
FROM [dbo].[tmpTransaction]

(3 row(s) affected)

If the data is coming from an external file, such as a text file, and the file is being imported to a table where one of the columns has a data type of SMALLDATETIME and this error is encountered, one way to work around this is to load the data from the external file into a temporary table first where the date column will be defined as VARCHAR then follow the steps provided above in loading the data from this temporary table into the destination table containing the SMALLDATETIME column.

• Frequently Asked Questions - SQL Server Error Messages
• Tips & Tricks - SQL Server Error Messages 1 to 500