1

Is there a way in SQL to ignore records for which a conversion failed. Setting some default value would also work. 

> SELECT convert(datetime, foo_str)
> FROM foo_tbl

 ------------------------
 Aug 23 2013 00:00:00.000
 Aug 17 2013 00:00:00.000
 May 06 2015 00:00:00.000
 Aug 13 2013 00:00:00.000
 Aug 09 2013 00:00:00.000
 Sep 05 2007 00:00:00.000
 May 06 2015 00:00:00.000
 May 06 2015 00:00:00.000
 Feb 24 2009 00:00:00.000
 May 06 2015 00:00:00.000
 Mar 29 2013 00:00:00.000
 May 06 2015 00:00:00.000
 Jul 24 2010 00:00:00.000
 May 06 2015 00:00:00.000
 May 06 2015 00:00:00.000
 May 03 2015 00:00:00.000

Msg 249, Level 16, State 1 , Line 1 Syntax error during explicit conversion of VARCHAR value '10101' to a DATETIME field.

Can the error be avoided so that the SELECT statements comes to completion?

Robert
  • 25,425
  • 8
  • 67
  • 81

1 Answers1

2

Something like below code should work on sybase

SELECT convert(datetime, foo_str)
FROM foo_tbl
WHERE foo_str like '[A-Z][A-Z][A-Z] [0-1][0-9] [0-2][0-9][0-9][0-9] [0-2][0-9]:[0-5][0-9]:[0-5][0-9].[0-9][0-9][0-9]'

if 00:00:00.000 is constant you can do it simpler

SELECT convert(datetime, foo_str)
FROM foo_tbl
WHERE foo_str like '[A-Z][A-Z][A-Z] [0-1][0-9] [0-2][0-9][0-9][0-9] 00:00:00.000'

or 

SELECT convert(datetime, foo_str)
FROM foo_tbl
WHERE foo_str like '[A-Z][A-Z][A-Z] [0-1][0-9] [0-2][0-9][0-9][0-9]%'
Robert
  • 25,425
  • 8
  • 67
  • 81
  • brilliant, my dates are in the YYYYMMDD format, so I adapted the like string to that, but thanks a ton really –  May 14 '15 at 08:45
  • Beware that the regular expression used here checks only if the value looks like a `datetime` but not if it "quacks" like one. For example if the month name is not valid, you will still get the error. – B0Andrew May 14 '15 at 08:58