June 29, 2009 at 8:49 am
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.
June 30, 2009 at 8:03 am
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.
June 30, 2009 at 9:10 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply