Delphi6, TADOQuery, DB2 iSeries, 01/01/001 DateTime

[xpress4]

We are having an issue with Delphi 6.

Our data is on an AS400 and we have a table that has fields defined as a datetime.

In delphi we created a new application and drop down the TADOConnection and a TADOQuery.

In the TADOQuery we select the datetime field and open the TADOQuery and it opens just fine.

When we try to work with the field, ie:
if ADOQuery1fldDATE.AsDateTime = StrToDate('01/01/0001') then (or)
if ADOQuery1fldDATE.Value = StrToDate('01/01/0001') then
we have issues when the value is '01/01/001', delphi blows up with an EOleException, Multistep operation generated errors.

Im wondering if anyone would know how to fix this so '01/01/0001' would be a valid date since it really is valid on the AS400, just not in delphi.

Thanks for any help
-jason

 

[roo0047]

Delphi has built in date validation in most all of its date routines and WILL error on a date as you described, regardless of where it came from.

I would create a conversion function for dates coming from AS400. I'm NOT AS400-wise so this is just theoretical, not literal:

Function AS400Date2DelphiDate(As400Date: string): string;
begin
  result:= StringReplace(As400Date, '/000', '/200', [rfReplaceAll]);
end;

  ...
  if ADOQuery1fldDATE.AsDateTime = StrToDate(AS400Date2DelphiDate('01/01/0001')) then 
  ...

This assumes that an AS400 year of "0001" is really year "2001".

You could also combine the functions like this:

Function AS400Date2DelphiDate(As400Date: string): TDateTime;
begin
  result:= StrToDate(StringReplace(As400Date, '/000', '/200', [rfReplaceAll]));
end;

  ...
  if ADOQuery1fldDATE.AsDateTime = AS400Date2DelphiDate('01/01/0001') then 
  ...

Roo
Delphi Rules!

 

[xpress4]

Thanks for the input.
The problem is as soon as you try to read the field in any shape or fashion, with-in Delphi it bombs out.
The only way we found around it was to rewrite the SQL and cast that value into a string.
Or change the ODBC to say treat dates as strings.

The best thing we have found is to stop using the ADO components and just use the standard database components and change the Alias to the ODBC driver we want to use.
Then the '01/01/0001' works just fine and is defined as a date.

Thanks again