'SQL Error : Conversion failed when converting date and/or time from character string

I'm trying to convert a varchar column to date or datetime but I don't understand why it's not possible it should work and I don't see any kind of error.

The column values is 31-07-2017 and the type is Varchar(250). I tried convert and cast and I get the same error:

Conversion failed when converting date and/or time from character string.

Does anyone have any idea on why it's like this?

sqlsql-server


Solution 1:[1]

The conversion error is because your session setting is other than DATEFORMAT dmy or varchar values do not conform to DMY format.

For the latter case, run the query below to identify problem values:

SELECT YourDateColumn AS InvalidDate 
FROM dbo.YourTable 
WHERE
    YourDateColumn IS NOT NULL
    AND TRY_CONVERT(date, YourDateColumn, 103) IS NULL;

As @TimBiegeleisen mentioned in a comment, it is best to choose the most appropriate column type (date in this case) for the data to be stored. Not only will this avoid errors like this, it will improve performance and better ensure data integrity.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Dan Guzman

Related Questions