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.

Solution 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

Solution 2

You can use TRY_CONVERT or TRY_CAST functions

Solution 3

If you just want the date part then take a SUBSTRING and calculate the date as follows. This might get you the correct date part at least.

SELECT CONVERT(datetime, SUBSTRING(analysed, 0, 11), 103 )
FROM OIL_SAMPLE_UPLOAD

Comments

[sav]

I'd prefer not to make any changes to the database I'm using. ie: I'd prefer to to change SET DATEFORMAT dmy; unless the change only exists for the duration of the query.

[Mudassir Hasan]

This doesnot do any changes to database , it is only for checking duration query execution

[sav]

For this to work, I would need to SET DATEFORMAT to dmy.

[sav]

needs to be 11 characters SELECT CONVERT(datetime, SUBSTRING(analysed, 0, 11), 103 )

[sav]

I can fix this by using STUFF(STUFF(analysed, 4, 2, SUBSTRING(analysed, 1, 2)), 1, 2, SUBSTRING(analysed, 4, 2))

[tr3v]

Note that these functions are only available in SQL Server 2012 onwards (110)