Subject Re: [firebird-support] Date format using Delphi and Firebird
Author Helen Borrie
At 02:34 PM 12/11/2005 +0200, you wrote:
>What must I make sure of before inserting a record with a date field into a
>Firebird 1.5 table? I get the EConvertError error 'is not a valid date',
>using Delphi 7. The field is declared as a Date field in the table. I assign
>the field a value as follows -
> qryGen.ParamByName('workdate').Value := strtodate('2005/11/12 02:00:19
>What is the best strategy concerning dates when writing an application which
>connects to an FB database, especially when clients will have their own date
>formats on their desktops.

OK, first of all, Fb doesn't know about Microsoft's date formats. It has
its own (rather wide) selection of date literal formats that it approves of
for the date part. The time part is *only* a 24-hour clock time (no AM,
etc.). So a TIME type has the format hh:nn:ss.nnnn.

For dates, avoid slash separators altogether unless you are representing US
time (mm/dd/yy or mm/dd/ccyy).

For your internationatal date format (above) you would want 'ccyymmdd
hh:nn:ss' or 'ccyy-mm-dd hh:nn:ss' for safety.

But, if you are using Delphi, don't use date literals and
StrToDate/StrToDateTime at all if you can avoid it. In the first place
StrToDate isn't valid for a string that contains the time of day; but,
above all, those functions expect US date format. You have to mess around
with EncodeDateTime and DecodeDateTime even to get a string that Delphi
won't either throw an error on or store the wrong date, e.g. 2nd January
instead of 1st February.

Delphi's date controls (native VCL and descendants) have a Date property
which is a TDateTime (or descendant). Make use of it! Delphi will do all
the stuff, all you need to do is:

qryGen.ParamByName('workdate').AsDateTime := MyDateControl.Date;