mORMot Open Source

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Handling Unassigned dates

I'm planning on using the date 0/0/0 as a sort of NULL value but have a problem with TTimeLog to TDateTime conversions.

function Iso8601.ToDate: TDateTime;
begin
  if Value=0 then
    result := 0 else
    result := EncodeDate((Value shr (6+6+5+5+4)) and 4095,
                         1+(Int64Rec(Value).Lo shr (6+6+5+5)) and 15,
                         1+(Int64Rec(Value).Lo shr (6+6+5)) and 31);
end;

Converts 0/0/0 to 30/12/1899. Could it be changed to:

function Iso8601.ToDate: TDateTime;
begin
  if Value=0 then
    result := -693594 else ...

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Handling Unassigned dates

It may be a modification impacting a lot of places in the framework source code.
For instance, it may break with some external database engines, which do not allow a date of 0/0/0.

Perhaps a better solution may be to introduce a dedicated process when a bound parameter (?) is set to nil in FormatUTF8/FillPrepare...
But it won't work for the ORM part...

Since there is no nullable values in Delphi (as in Java or C#), it is difficult to make something consistent with the ORM / object pascal code.
See NULL handling in the latest 1.18 SAD pf documentation.

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: Handling Unassigned dates

I guess using 0/0/0 isn't such a good idea. In my case business rules make certain dates invalid so I think I'll pick one of those as a 'magic' date to indicate unassigned.
Thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Handling Unassigned dates

I guess using 0/0/0 isn't such a good idea. In my case business rules make certain dates invalid so I think I'll pick one of those as a 'magic' date to indicate unassigned.

This was the TDateTime=0 value purpose, which reflects the Iso8601.Value=0 in the code quoted above.

Depending on the external database (and the SynDB*.pas implementation), it will be stored as null or 12/30/1899.
But since there is no nullable type in Delphi, it is not so easy.