At 09:54 PM 29/09/2005 +0000, you wrote:

>Hi All,
>
>I have an Delphi 5 application running against Firebird v1.5.2 and
>using IBO v4. I'm writing a process to archive old orders to a 2nd
>Firebird database. I'm creating a target database with a TIB_Script
>and then using 2 TIB_Connection components to connect to the source
>and target databases. I'm then using T_IBQuery objects to copy the
>data across. Everything is peachy and runs with no errors. However,
>after the copy is completed, I see corrupted date fields in my target
>orders table. Several dates will show in IbExpert as 12/30/1899. The
>dates are correct in the source databaes but the two queries
>(connected to the 2 different TIB_Connection objects show this.
>
>Source Target
>Order_Date = 12/30/1899 - OrderDate = 12/30/1899
>Ship_Date = 09/26/2001 - ShipDate = 09/26/2001
>Invoice_Date = 09/26/2001 - InvoiceDate = 09/26/2001
>
>What could I be doing wrong?

As others have explained, what you are seeing here is a Delphi thing, not
Firebird. Delphi DateTime is allowed to be 0 and, if it is, Delphi
interprets it as "Day 0", or 12/30/1899.

Firebird also has a "Day 0", which is 11/17/1899. However, Firebird does
not recognise the numeral 0 as a valid date, so you would have got
exceptions during your data pump if you had tried to store 0. Therefore,
we can conclude one of two things, either:

1) your data pump was done in Delphi and you have actually stored the
Delphi zero date in your null date fields; or

2) you do have nulls in your data and your current Delphi application code
is doing it

When reading Date columns you should test for null in Firebird data and (as
it appears) you might also have to test for Delphi DateTimes that are zero.

if qryTar.FieldByName('OrderDate').AsDate = 0 then
qryTar.FieldByName('OrderDate').Clear;

I'd want to use isql to verify that the affected dates are stored as null
and not as Delphi's date zero. If it turns out to be the case, you will
need to repair the data by running an ad hoc query in isql (not using
IBExpert, which is written in Delphi) like

update orders
set OrderDate = null
where OrderDate = '12/30/1899'

Of course, if you have defined any date field as non-nullable, then you
have problems. One way is redefine the column with a DEFAULT
constraint; but a better way is to force an order date in a Before Insert
trigger, e.g.

create trigger bi_orders for order
active before insert as
begin
if (new.orderdate is null) then
new.orderdate = CURRENT_DATE;
end

To allow this to happen in your IBO app, you will have to set the field's
REQUIRED attribute to False in ColumnAttributes.

Helen