I am converting a string type field to a date to be in 'yyyy-mm-dd' using convert(date,[field_name],101) AS ChangeDate

I have been using the same syntax in other queries and it works, but not this time.

I tried using convert(datetime2,[field_name] AS ChangeDate also producing the same error. Below is the intended result with ChangeDate to be in the desired date format.

The result does appears for few second before the error popup as below:

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

CodePudding user response：

Try this:

SELECT 
    CASE 
        WHEN ISDATE(field_name)=1 THEN CONVERT(datetime, field_name, 103 ) 
        ELSE null 
    END
FROM your_table

I think you need to check that your date is correct and after that convert it. You have a problem with your data.

Another solution:

Instead of convert use try_convert function:

try_convert(date,field_name,101) AS ChangeDate