How to filter FireDAC dataset by a DATE value constant?

Question

procedure TForm2.Button1Click(Sender: TObject);
begin
  with qryWithFilter do
  begin
    Filtered := False;
    OnFilterRecord := nil;
    // date separator may be used any from [' ', '-', '\', '.', '/']
    // Filter := 'DATA_NAS < (''DatetoStr(date3)'')';
    // Filter := 'DATA_NAS < ''28/06/1939''';
    // Filter := 'DATA_NAS < (FormatDateTime(''dd/mm/yyyy'', ''28/06/1968''))';
    // Filter := 'DATA_NAS < TO_DATE(''1996-07-29'',''yyyy-mm-dd'')';
    Filter := 'DATA_NAS < (TO_DATE(''1996-07-29'',''yyyy-mm-dd''))';
    Filtered := True;
  end;
end;

Only work with ''28/06/1968''. The error I'm getting is:

How can I filter my FireDAC query by a DATE value constant?

Answer 1

The FireDAC's Filter property implementation doesn't support DBMS functions, but offers you their own expression engine.

With FireDAC converting a string constant into a DATE type value in the Filter property expression can be as simple as using the CONVERT macro function or {d} escape sequence. The expression engine then picks a DBMS specific conversion function for you.

Try one of these filters (date string format for all variants is yyyy-mm-dd):

uses
  FireDAC.Stan.ExprFuncs;

procedure TForm1.Button1Click(Sender: TObject);
begin
  with FDQuery do
  begin
    { disable filter }
    Filtered := False;
    { filter with date escape sequence (is nicely readable) }
    Filter := 'DATA_NAS < {d 1996-07-29}';
    { filter with CONVERT macro function without function escape sequence }
    Filter := 'DATA_NAS < {CONVERT(''1996-07-29'', DATE)}';
    { filter with CONVERT macro function with function escape sequence }
    Filter := 'DATA_NAS < {fn CONVERT(''1996-07-29'', DATE)}';
    { enable filter }
    Filtered := True;
  end;
end;