CODEWITHSUNDEEP
sqlsql-servert-sql
neeko
neeko

Reputation: 2000

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

Upvotes: 1

Views: 40669

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

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

Upvotes: 5

Related Questions