CODEWITHSUNDEEP
sqlsql-server
No-name
No-name

Reputation: 83

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?

Upvotes: 2

Views: 40134

Answers (2)

No-name
No-name

Reputation: 83

I found the issue, it's a very very stupid mistake, there was one column with a text format I couldn't see it because I checked with length, but the SQL gives max length so I used group by and manually check dates until I found one column that's not correct in 1 m account records

Upvotes: 2

Dan Guzman
Dan Guzman

Reputation: 46193

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.

Upvotes: 6

Related Questions