How to handle date conversion error in SQL?

Question

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.

Answer 1

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

Answer 2

You can use TRY_CONVERT or TRY_CAST functions