At 09:27 PM 11/08/2004 +0000, you wrote:

>Hello
>
>I've got a problem with a master detail relationship, where
>
>the master dataset is:
> SELECT DISTINCT WDATE FROM WAYBILLS;
>
>the detail dataset is :
> SELECT E.NAME, C.TYPE, C.PLATE , W.WDATE,
> FROM EMPLOYEES E, CARS C, WAYBILLS ');
> WHERE M.ALKID=A.ID AND M.RENDSZAM=J.RENDSZAM');
>
>and the most important thing(I guess): the Masterlinks property is:
> WDATE=WDATE
>
>I get the following error message with the settings above:
> EConvertError, '2003/07/29 00:00:00.0000 is not a valid date and
>time'
>
>I find it strange because the error message looks like as if I was
>working with timestamps and I used an invalid one, but I have
>nothing to do with timestamps but simple dates.

The exception is a Delphi one, not an SQL one. Delphi typecasts the Fb/IB
data types as close as possible to the data types it supports. By default,
it casts both dates and timestamps as TDateTime.

It gets your DATE type as a variant. The Fb/IB date comes through with
zero values in the time segment, including the tens of thousands of
milliseconds sub-second portion. Delphi's Timestamp supports only to
milliseconds.

Keying and linking things on dates is fraught with problems, some soluble,
some not. Borland software was never friendly about non-US date formats, so
earlier Delphi versions misbehave more than later ones. Very old IBO
versions simply don't recognise the ODS 10 SQLTypes. So both Delphi and
IBO versions might be a factor.

Tell us whether you are using native TIB_ components or the TDataset (TIBO)
ones. It may make a difference.

And, of course, the Windows locale affects things somewhere along the line,
since that's the only way the error handler could have got hold of a date
literal in ccyy/mm/dd format.

>Any ideas?

The easiest thing would be to find a way to avoid using date types as
KeyLinks and MasterLinks. Beyond that, with more information about the
environment, ways and means might be found.

Try forcing the date columns to be cast as TDate. It still won't
necessarily solve the problem, though, because a "raw" date portion under
the hood in Delphi and IBO is mm/dd/ccyy, whereas, at some level, your
dates are being changed to ccyy/mm/dd.

The ccyy/mm/dd format is itself not a legal date literal format for
Fb/IB. Problems could arise at the point where IBO is constructing the
parameter for the MasterLinks, since it involves casting dates to strings
to dates, back and forth. It will try to find the detail record using the
parent's WDATE and pass a WHERE clause like MLNK_WDATE_0 = ? If the value
for the parameter resolves to a literal like '2003/07/29', you'll get an
SQL exception.

Helen