SQL Server Error Messages - Msg 295¶

Error Message¶

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

Causes¶

The SMALLDATETIME data type is used for representing the date and the time of day between January 1, 1900 through June 6, 2079, with an accuracy of up to 1 minute. SMALLDATETIME values are stored as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900 while the second 2 bytes store the number of minutes since midnight.

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.