CODEWITHSUNDEEP
sqlsql-serverdateselectwhere-clause
sm86
sm86

Reputation: 87

conversion failed when converting date and/or time from character to string in where clause

I had a table where the service_date was char(8), like 20191007. I did a convert(date,service_date) as new_date and it seemed to convert it to date data type. When I try and use convert(date,service_date) >= '2019-10-07' I get the "conversion failed when converting date and/or time from character to string" error. What do I need to change to get it to work?

Thanks.

Upvotes: 0

Views: 379

Answers (1)

GMB
GMB

Reputation: 222462

The string format in which you are storing your dates makes it possible to filter against a sring literal, so I would just use:

service_date >= '20191007'

This is more efficient than using a conversion function, since it can take advantage of an index on the column, if any.

However, as commented by John Cappelletti, this might produce unexecpted results if your data is not in the right format. In that case, use try_cast():

try_cast(service_date as date) >= '20191007'

try_cast() attempts to convert the string to the target datatype, and yields null if the conversion fails.

Upvotes: 2

Related Questions