selecting rows from sql server where a casting issue occurs

Question

Hi everyone,

i was just wondering if anyone knows how to select rows where a specified column will come under a casting issue.

ie.

SELECT * FROM ThisTable t

WHERE 0 <> ( select cast(t.value as datetime) )

the 'select cast(t.value as datetime)' would ideally return the result of @@error to indicate the casting issue has occurred.

does anyone have any idea, because im bloody stumped.

Cheers

Answer 1

SELECT * FROM ThisTable t

WHERE ISDATE(t.value) = 1 -- or 0 for NOT a datetime valaue

ISDATE is your friend, if I understand correctly...

Answer 2

I was also looking for a solution to avoid errors when converting varchar to datetime in SQL server.

I have a varchar column containing dates with inconsistent entries like DD.MM.YYYY or MM.YYYY. With a simple cast, the query works correctly only until it encounters a record with the former format, which causes a cast error and interrupts the query. Using your suggestion, I can easily catch the MM.YYYY values and set them to null, which is exactly what I want.

SELECT CASE WHEN ISDATE(contract_date) = 1 
            THEN CAST(contract_date AS datetime)
            ELSE NULL
       END converted_dates
FROM contracts

"01.01.2009" gets successfully converted to "01.01.2009 00:00:00" whereas "01.2009" is returned as NULL.