mORMot Open Source

dieforwhat

Member

Registered: 2012-06-17

Posts: 6

TDate Problem

Hi everyone.I've problem with TDate field.Is there any example about using Tdate but not TdateTime  and is it possible to save Tdate like  sqlite julianday ?As I know Tdate is saved like single precision floating number and not matching either sqlite datetime nor date function.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: TDate Problem

First of all, why do you want to use SQlite3 format?
You can easily write an INTEGER or FLOAT field to hold this kind of data in mORMot.
In fact, SQLite3 has no native date format for storage, just functions to handle ISO 8601 textual dates.
http://www.sqlite.org/lang_datefunc.html
In mORMot, TDateTime will be stored as TEXT, just as expected by SQLite3.

And like in Delphi, a TDate is a TDateTime with frac(aValue)=0.

You should better not rely on SQLite3 for handling date and time, but use DB-indenpendent date and time handling, at the ORM level, i.e. at Delphi code level, using a TDateTime parameter.
Then you can use the FLOAT/double/TDateTime content to make comparisons in your queries.
Therefore, it will work with every DB back end it will run on.

See also http://synopse.info/forum/viewtopic.php?id=719
and http://synopse.info/forum/viewtopic.php?id=461
and others entries in the forum.

It is difficult to guess what is your exact problem.

dieforwhat

Member

Registered: 2012-06-17

Posts: 6

Re: TDate Problem

Do you mean TDate is fractionless?But Tdate fields have fraction in db (float type) .How  we can query for example if that field hold date value( no hour no minute no second) without rounding?Can you give some examples?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: TDate Problem

In mORMot, if you define a published property of a TSQLRecord as TDateTime, it will be stored as TEXT, with ISO-8601 encoding.
So all date/time SQLite3 functions will work as expected.
This is for the SQL side (i.e. the WHERE clause of a query).
In mORMot, TDateTime is not stored as DOUBLE.
You should NOT use TDate when defining a TSQLRecord published property.

On the Delphi side, TDate is in fact the same as TDateTime, but is expected to be handled by code ignoring time.
So if your TDateTime property was filled as a TDate (i.e. Frac(aDateTime)=0), it will be stored only as date in the DB ('2012-10-30') so will be handled just as a pure date value.