CODEWITHSUNDEEP
sqlsql-serversql-server-2005castingtypes
user155548
user155548

Reputation: 3

selecting rows from sql server where a casting issue occurs

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

Upvotes: 0

Views: 237

Answers (2)

ercan
ercan

Reputation: 1716

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.

Upvotes: 1

gbn
gbn

Reputation: 432261

SELECT * FROM ThisTable t

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

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

Upvotes: 2

Related Questions