Date from AS400

[JPMontreal]

Good morning all,

In my AS400 source I have tables with date fields, if I import a table with a package to a text file, there are dates like “0001-01-01”. Before I import directly my AS400 tables into Access without problems because Access read those date as “1901-0-01”. Now we have to move to a SQL server. If I try to import with SQL Package it crash unless I first change the fields in my SQL table to varChar(15), do the importation, change the value “0001-01-01” to “1901-01-01” and then alter the data type back to smalldatetime. I have a lot of tables with numerous date fields. I try to link the AS400 source to my SQL Server, I still could not read tables that have dates like “0001-01-01”. I could not do modification to the AS400 source. Is there a way to solve that problem?

Thanks.


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[Narizz28]

I had the exact same problem that I found that the dates before 1901-01-01 make SQL freak out. I resolved by altering the column first to a varchar, importing, then deleteing anything less than 1901-01-01, then altering back to a datetime field.

 

[MDXer]

You could use the ActiveX Transform Object and Search and replace 0001-01-01 or COndition the transform with ISDate() would be smoother and probably faster than using varchar

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[Narizz28]

MDXer,

Intriguing. I have a couple of questions about that though.

1.) Aren't ActiveX Transforms painfully slow?

2.) What should the date be replaced with to show it an invalid? My first thought is just straight NULL.

 

[MDXer]

yes the activex transform can be painfully slow but then so can the overhead of loading into an improper datatyped column and then scanning it to delete. THe only way of telling is by testing it. You could replace with null or is you know the date 1901-01-01 will never appear in your data you could set it to that.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[MDXer]

I guess I should clarify that I am reffering to using the activex task in the datapump transform if that is how you are importing your data into SQL.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[JPMontreal]

Thanks,
When I import with package, I could go around it. But I need to be live, so, from my SQL Server, I link into the AS/400. If I try to open a AS/400 table that has “0001-01-01” date, it said “conversion failure”.

Regards,
Jean-Paul


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[john76]

I resolved 0001-01-01 dates as follows:

SELECT  ...,
        CASE  ISDATE(AS400DateField)
          WHEN 1 THEN AS400DateField
          ELSE  CONVERT(datetime, '01/01/1753)
        END  AS  SQLDateField,
        ...
  FROM  AS400.yaddayadda

Hope this helps,
John

 

[JPMontreal]

Hi john76,

Unfortunately your solution didn’t work for me but it put me on the right track. The following work find:

SELECT * FROM OPENQUERY(US400, 'SELECT YEAR(VDTLPDCY)as y, month(vdtlpdcy) as m, day(vdtlpdcy) as d FROM AVM order by y')

Thanks.


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[john76]

That's it. Not sitting in front of my terminal or I would have opened the query.

Glad I could send you in the right direction though.

John