neeko neeko - 1 month ago 8
SQL Question

Conversion failed when converting date and/or time from character string even though result is correct

This is my query:

set dateformat mdy;
select cast([File Date] as date) as 'test' from gpdetail


which produces this error:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.


Any idea how to get rid of the error? any help much appreciated have been stuck on this for ages :(

Under the results tab it shows the correct results
However, Under the results tab it shows the correct results

this is the actual data
this is the actual data

Answer

The place to start is with the isdate() function:

select [File Date]
from gpdetail
where isdate([File Date]) = 0;

This may find places where the date does not conform.

If you just want to ignore badly formatted strings, then try:

select (case when isdate([File Date]) = 1 
             then cast([File Date] as date) 
        end) as test
from gpdetail