TZQuery.AsDateTime property

[peterd]

Hi,

Using SQLite v3.6.22
ZeosLib v6.6.6
Delphi 6

I have created a simple SQLite table called user with one DATETIME field called t.
When I try to assign a TDateTime variable dt to one of the field values using

dt := ZTable1.FieldByName ('t').AsDateTime;

there is no problem.

Actually I want to extract the maximum value of t from the table so I tried

ZQuery1.SQL.Text := 'select max (t) as maxt from user';
ZQuery1.Open;
dt := ZQuery1.FieldByName ('maxt').AsDateTime;

Now I get a conversion error: '2007-11-01 20:33:41 is not a valid date and time'.

Does anyone know what I'm doing wrong?

Thanks

Peter

[Wild_Pointer]

Hello, peterd,
This is wild guess, but what date format is used on your computer (locale). It seems that the error is related to different formats on your computer and the DB server. Try changing your locale date time formate to YYYY-MM-DD HH:NN:SS. If that helps, then it is for sure date/time conversion error.

Good luck to you!

[peterd]

Thank you W_P for your reply.

You're right about the local settings. But isn't it strange that a property as AsDateTime depends on the local setting. As far as I know one of the reasons the TDateTime type is used is not to depend on the local settings.
Another point is that TZTable doesn't seem to have this problem.

Maybe somebody knows if it's possible to change the local settings temporarely (during the execution of 'AsDateTime' assignment).

Thanks

Peter

[mdaems]

Not sure if sqlite can return the result of max(t) as a datetime value to zeoslib. That may be why getting asdatetime is impossible.

You can try to debug at TZSQLiteResultSet.Open ?

Mark

[peterd]

Mark,

ZQuery1.FieldByName ('maxt').AsDateTime works only if the short date format is set to yyyy-mm-dd, which doesn't make it very useful in practice.

A workaround may be:

var
OldShortDateFormat: String;

OldShortDateFormat := ShortDateFormat;
ShortDateFormat := 'yyyy-mm-dd';
dt := DataMod.qEvent.FieldByName ('start').AsDateTime;
ShortDateFormat := OldShortDateFormat;

This works on my 'dutch' PC. Still have to check other languages.

Peter

[mdaems]

Peter,

The question remains: is this because the field format returned by sqlite is different for "select max(t) from" and "select t from"?
Sounds like this must be the reason as the result for the second query is correct.

Please debug in the procedure I pointed you to.

If the datatype is different: is there a function to force the resultset datatype like cast() in SQLite?

Mark
(BTW : you can use my email if you want assistance in dutch)

[peterd]

Marc,

What is your email address?

Peter