How can I filter a dataset in Delphi on a TDateTime field

I have a dataset that I need to filter by time. e.g. I want records where the time contained in a TDateTime field is between 6:00 AM and 6:00 PM.

My dataset contains a field named time_of_day, of type TDateTime. The value assigned to the filed is a 开发者_运维知识库time, with no date.

I have tried creating the filter string using both the string representation of the time (time_of_day >= '6:00 AM' and time_of_day <= '6:00 PM'), and also using the floating point representation (time_of_day >= 0.25 and time_of_day < 0.75), but neither have worked.

I need to know how to construct the filter string to assign to the TDataSet.Filter property.

I think it might depend on short time format but this works for me using TADODataSet.

ADODataSet1.Filter := 'TimeField = 11:00:00';
ADODataSet1.Filtered := True;

A more full proof solution is to use the OnFilterRecord event instead.

procedure TForm4.ADODataSet1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
begin
    if DataSet.FieldByName('TimeField').AsDateTime = FilterValue then
        Accept := True
    else
        Accept := False
end;

If you need interval it would look like this.

ADODataSet1.Filter := 'TimeField >= 10:00:00 and TimeField < 11:00:00';
ADODataSet1.Filtered := True;

OnFilterRecord

procedure TForm4.ADODataSet1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
begin
    if (DataSet.FieldByName('DateField').AsDateTime >= FromTime) and
         (DataSet.FieldByName('DateField').AsDateTime < ToTime) then
        Accept := True
    else
        Accept := False
end;

If you use AMPM notation you need to enclose the time in '

ADODataSet1.Filter := 'TimeField >= ''10:00 AM'' and TimeField < ''11:00 AM''';
ADODataSet1.Filtered := True;

But I would really recommend the OnFilterRecord instead so you do not have to worry about time formats.

TDateTime in Delphi is type double, where the integral portion is the number of days since 12/30/1899 and the fractional (decimal) portion is the time.

A time of noon (without a date portion) would be indicated as 0.5, which means that 6:00 AM would be 0.25 (exactly, 1 day / 24 hours * 6 hours) and 6:00 PM would be 0.75. The simplest way to filter, then, would be:

// 6 hours after midnight
Frac(YourTimeField.AsFloat) >= (1.0 / 24.0) * 6 and
// 18 hours after midnight
Frac(YourTimeField.AsFloat) <= (1.0 / 24.0) * 18; 

Doing the actual calculation makes sure that if the number can't be exactly represented as 0.25 (like 0.2499999999999), you still get the correct value to compare.

Frac returns the fractional portion, just to make the code clearly indicate that you're only considering the time portion of the value.

I don't use ADO but Zeos, but rendering as float

  xx.filter:='datefieldname > '+floattostr(strtodatetime(edit1.text));

works for me.