How to use the Filter functions of TClientdatasets for dates?

Question

I have a TClientDataSet in Delphi 7 and I'd like to apply a filter which I type into a simple TEdit, so it looks like this:

CDS.Filter:=Edit1.Text;
CDS.Filtered:=True;

Now I looked at the Helpfile for filtering records and according to it I should be able to Filter DateTime-Fields as well. But whenever I write something like this into my Edit:

DAY(EDATUM)=17  

and apply the filter I get a "Type Mismatch in Expression"-Exception.

I have tried numerous different formats of the example above.

DATE(DAY(EDATUM))=DATE(DAY(17))     //Doesn't work
DAY(EDATUM)='17'                    //Doesn't work
DAY(EDATUM)=DAY(17)                 //Doesn't work   
DAY(EDATUM)=DAY(DATE('17.09.2016'))
...
...

the only one that works is

EDATUM='17.09.2016'                 //Works

But I want to filter on Days months and years seperately and not have them together in a string.

Nothing I found online elsewhere worked either.

Any Idea what I'm doing wrong?

Edatum is a TimeStamp in a Firebird 1.5 Database.

Answer 1

If you want to use a Filter expression instead of an OnFilterRecord handler, it is worthwhile taking a look at the source of the TExprParser class, which is what TClientDataSet uses for textual filters. It is contained in the DBCommon.Pas unit file in your Delphi source. The D7 TExprParser supports the following functions:

function TExprParser.TokenSymbolIsFunc(const S: string) : Boolean;
begin
  Result := (CompareText(S, 'UPPER') = 0) or
            (CompareText(S, 'LOWER') = 0) or
            [...]
            (CompareText(S, 'YEAR') = 0) or
            (CompareText(S, 'MONTH') = 0) or
            (CompareText(S, 'DAY') = 0) or
            [...]
end;

Btw, it is worthwhile looking through the rest of TExprParser's source because it reveals things like support for the IN construct found in SQL.

On my (UK) system, dates display in a DBGrid as dd/mm/yyyy. Given that, all of the filter expressions shown below work in D7 without producing an exception and return the expected results:

procedure TForm1.Button1Click(Sender: TObject);
begin

  //  ADate field of CDS is initialised by
  //  CDS1.FieldByName('ADate').AsDateTime := Now - random(365);

  edFilter.Text := 'ADate = ''10/2/2017''';  //  works, date format = dd/mm/yyyy
  edFilter.Text := 'Month(ADate) = 2';       //  works
  edFilter.Text := 'Year(ADate) = 2017';     //  works
  edFilter.Text := '(Day(ADate) = 10) and (Year(ADate) = 2017)';        //  works

  CDS1.Filtered := False;
  CDS1.Filter := edFilter.Text;
  CDS1.Filtered := True;
end;

If you don't get similar results, I'd suggest you start by looking at your regional settings and how dates are displayed in a TDBGrid.

Filter expressions are not particularly efficient compared to the alternative method of filtering, namely to use the OnFilterRecord event.

In the event handler, you can use e.g. DecodeDateTime to decode it into its Year, Month, Day, etc components and apply whatever tests you like to their values. Then set Accept to True or False.

Update I gather from your comment to an answer here Delphi: check if Record of DataSet is visible or filtered that the problem you had with this was that the date functions supported by TExprParser.TokenSymbolIsFunc() are not in your user's language.

You can use the code below to translate the date function names in the filter expression. See the embedded comments for explanation of how it works

type
  TForm1 = class(TForm)
    [...]
  public
    NameLookUp : TStringList;
    [...]
  end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  NameLookUp := TStringList.Create;
  //  Assume Y, M & C are the local-language names
  NameLookUp.Add('Y=Year');
  NameLookUp.Add('M=Month');
  NameLookUp.Add('D=Day');
  [...]
end;

procedure TForm1.Log(const Title, Msg : String);
begin
  Memo1.Lines.Add(Title + ' : ' + Msg);
end;

function TForm1.TranslateExpression(const Input : String; ADataSet : TDataSet) : String;
var
  SS : TStringStream;
  TokenText : String;
  LookUpText : String;
  Parser : TParser;
  CH : Char;
begin
  SS := TStringStream.Create(Input);
  Parser := TParser.Create(SS);
  Result := '';
  try
    CH := Parser.Token;
    //  following translates Input by parsing it using TParser from Classes.Pas
    while Parser.Token <> #0 do begin
      TokenText :=  Parser.TokenString;
      case CH of
        toSymbol : begin
          //  The following will translate TokenText for symbols
          //  but only if TokenText is not a FieldName of ADataSet
          if ADataSet.FindField(TokenText) = Nil then begin
            LookUpText := NameLookUp.Values[TokenText];
            if LookUpText <> '' then
              Result := Result + LookUpText
            else
              Result := Result + TokenText;
          end
          else
            Result := Result + TokenText;
        end;
        toString :
          //  SingleQuotes surrounding TokenText in Input and ones embedded in it
          //  will have been stripped, so reinstate the surrounding ones and
          //  double-up the embedded ones
        Result := Result + '''' + StringReplace(TokenText, '''', '''''', [rfReplaceAll]) + '''';
        else
          Result := Result + TokenText;
      end; { case }
      if Result <> '' then
        Result := Result + ' ';
      CH := Parser.NextToken;
    end;
  finally
    Parser.Free;
    SS.Free;
  end;
  Log('TransResult', Result);
end;

procedure TForm1.btnSetFilterExprClick(Sender: TObject);
begin
  //  Following tested with e.g edFilter.Text =
  //  LastName = 'aaa' and Y(BirthDate)  = 2000
  UpdateFilter2;
end;

procedure TForm1.UpdateFilter2;
var
  T1 : Integer;
begin
  CDS1.OnFilterRecord := Nil;
  T1 := GetTickCount;
  CDS1.DisableControls;
  try
    CDS1.Filtered := False;
    CDS1.Filter := TranslateExpression(edFilter.Text, CDS1);
    if CDS1.Filter <> '' then begin
      CDS1.Filtered := True;
    end;
    Log('Filter update time', IntToStr(GetTickCount - T1) + 'ms');
  finally
    CDS1.EnableControls;
  end;
end;