Date conversion error from DB2 to SQL 2005

• Steve Hoyer

  SSC Eights!

  Points: 992

  More actions

  June 29, 2009 at 8:49 am

  #135472

  Good morning,

  I have SQL 2005 linked via the OLE DB for ODBC --> iSeries Access ODBC Driver to our AS/400. If I query only non-DATE fields, it works like a charm. If I include a date field I get the error "Error converting data type DBTYPE_DBTIMESTAMP to datetime."

  I know my dates in this table on the 400 default to '0001-01-01'. This, I presume, is my problem and I need to handle these dates. I've tried the following, and can't get it to work.

  ,cast(A4CCDZ as char(10)) as A4C

  ,cast(A4CCDZ as char(26)) as A4C

  ,A4C =CASE A4CBDZ

  WHEN '0001-01-01' THEN ''

  ELSE A4CBDZ

  END

  ,NULLIF(A4CBDZ,'0001-01-01') as A4C

  I have no control over what is in the original DB2 database. I just need to either set this field as null or '' or some other identifiable date/string value so they can be pulled out in reporting.

  tia,

  Steve.

• Steve Hoyer

  SSC Eights!

  Points: 992

  More actions

  June 30, 2009 at 8:03 am

  #1017586

  So this seems to work...

  SELECT CAST(NULLIF(A4CCDZ, '0001-01-01') AS DateTime) AS A4CCDZ

  FROM OPENQUERY(link400, 'SELECT CHAR(A4CCDZ) as A4CCDZ FROM Schema.Object')

  Unless the field contains bad data (i.e. '1003-09-27'). Guess that's the next step.

  Steve.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  June 30, 2009 at 9:10 am

  #1017649

  How about just bringing it all across as character into a temp table and then do your conversions in SQL Server. You can then use the IsDate Function to convert all the non-valid SQL Server dates to NULL.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question