How to handle date conversion error in SQL?

error-handling, sql, string, t-sql, type-conversion

Solution

For SQL Server you can use ISDATE() function to check whether value is valid date

SELECT CASE WHEN ISDATE(analysed)=1 THEN CONVERT(datetime, analysed, 103 ) 
            ELSE '' END
FROM OIL_SAMPLE_UPLOAD

Problem

So I'm trying to convert strings in an SQL databse into datetime values. I have some dates in a table like this: ``` 23/12/2013 16:34:32 24/12/2013 07:53:44 24/12/2013 09:59:57 24/12/2013 12:57:14 24/12/2013 12:48:49 24/12/2013 13:04:17 24/12/2013 13:15:47 24/12/2013 13:21:02 24/12/2013 14:01:28 24/12/2013 14:02:22 24/12/2013 14:02:51 ``` They are stored as strings unfortunately And I want to convert them to datetime ``` SELECT CONVERT(datetime, analysed, 103 ) FROM OIL_SAMPLE_UPLOAD ``` However I get this message when I run the query The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Presumably because some values are badly formed (although I am yet to spot any of these) It's ok if some values don't convert, I just need a way of handling this situation. Something like ISNULL(CONVERT(datetime, analysed, 103 )) would be good except that the convert function does not return NULL when it fails.

Original source