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.

to be clear the strings are in dd/MM/yyyy hh:mm:ss format

sav
– sav

2014-01-29 04:31:53 +00:00
Commented Jan 29, 2014 at 4:31 — sav
– sav, Commented Jan 29, 2014 at 4:31

Mudassir Hasan · Accepted Answer · 2014-01-29 04:25:18Z

28

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

answered Jan 29, 2014 at 4:25

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sav Over a year ago

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 Over a year ago

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

sav Over a year ago

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

sav Over a year ago

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

Lev Z · Accepted Answer · 2014-12-15 13:00:02Z

13

You can use TRY_CONVERT or TRY_CAST functions

answered Dec 15, 2014 at 13:00

Lev Z

81210 silver badges19 bronze badges

1 Comment

tr3v Over a year ago

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

user2989408 · Accepted Answer · 2014-01-29 04:46:19Z

1

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

edited Jan 29, 2014 at 4:46

answered Jan 29, 2014 at 4:33

user2989408

3,1371 gold badge20 silver badges15 bronze badges

1 Comment

sav Over a year ago

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

Collectives™ on Stack Overflow

How to handle date conversion error in SQL?

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related