Variant misrepresentation for endoftheday

Question

(Background) I have a program that is using a parameterised query on a database searching on time of day. The required functionality means that I do some searches where the time is the end of the day so I natural have code like the following

Query.Parameters[3].Value := TimeOf(EnfOfTheDay(ToTime));

This fails with a formatting error because the time comes through as "31/12/1899". This is demonstrated by the following console program which outputs "31/12/1899" on the system console (or the equivalent in your regional settings).

program Project1;
{$APPTYPE CONSOLE}
{$R *.res}

uses
  System.SysUtils, System.DateUtils;
var
  V: variant;
  Time: TDateTime;
begin
    Time := TimeOf(EndOfTheDay(EncodeDate(2017,1,26)));
    V := Time;
    Write(Output, V);
    ReadLn;
end.

This is easy enough to work around but my question is whether this is a fault that should be reported to Embarcadero. I can see that the problem specifically occurs when a time only value in a TDateTime type is stored into a variant which is then coerced to text. Times earlier in the day work fine yielding strings like "23:59".

If I change the Time variable to a TTime then the resulting string is the numeric fraction (ie the Variant hasn't been set up as a datetime value) but I don't understand why the particular fraction equal to 23:59:59 (which is what EndOfTheDay generates) is interpreted as the 1899 date. I'm innately suspicious of anything yielding 1899 rollover dates because of the special issues with Microsoft products that may mean this is intentional.

Answer 1

The variant representation will be set to varDate, which internally is a TDateTime variable. So the information is still kept inside the variant.

WriteLn(Output, FormatDateTime('hh:nn:ss',V)); 

outputs

23:59:59

The error is to be found on the variant VarToStr routine that eventually calls:

function DateToWStrViaOS(const AValue: TDateTime): WideString;
begin
  VarResultCheck(VarBStrFromDate(AValue, VAR_LOCALE_USER_DEFAULT, 0, Result),
                 varDate, varOleStr);
end;

VarBStrFromDate is a call to the OS, that somehow rounds the value to 1, hence the date "31/12/1899".

---

Conclusion, if you still want to use variants in your database, don't use the built-in variant to text conversions for TDateTime supplied by the variant library.

---

Update: EndOfTheDay will return a time that is 1 millisecond before midnight. It seems that the variant resolution when converted to text is based on seconds (windows design). Setting the time to 1 and subtracting with Time := IncMilliSecond(Time,-501) will return the correct value. (Or Time := IncSecond(Time,-1) if you like.)

Answer 2

If you rewrite your example as

begin
    Time := Now; //TimeOf(EndOfTheDay(EncodeDate(2017,1,26)));
    Time := EndOfTheDay(Time);
    V := Time;
    WriteLn(Output, V);
    Time := TimeOf(Time);
    V := Time;
    WriteLn(Output, V);
    ReadLn;
end.

put a watch on the Time variable and single-step it in the debugger, it's evident that it's the call to TimeOf that causes the unhelpful result. The reason is as indicated in LU RD's answer which appeared while I have been writing this but has since been deleted (if it reappears I'll take this down), namely that TimeOf sets the integral part of a datetime value to zero, which is represented as "31/12/1899" by default. But that reason is not, imo, the problem: the problem is assigning the result on TimeOf to a variant and leaving it to the RTL to produce its representation.

Going back to your problem which prompted your q in the first place, probably a better way to address this, if your db's SQL implementation supports it, is to use a construct/function which deals with the time component only of however its dates/times are stored.

Btw, I think your q title is wrong, the problem isn't 'variant misrepresentation for endoftheday'.