sav sav - 2 years ago 122
SQL Question

How to handle date conversion error in SQL?

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 )

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download