Filter Date Data

anuchit · 2019-02-27 02:44:41

i use Filter Data type String , Integer in mORMot it's no problem.
but i Filter type Date Data not found.
how to use Filter Date Data. thank you so much.
SQL Server DB

edwinsn · 2019-02-27 04:28:13

The following works perfectly for SQLite for me, but since mORMot persists datetime fields as ISO8601 I think it most probably also works with MS SQL.

function GetDateTimeRangeFilteringSqlClause(const aFieldName: String; const aStartTime, aEndTime:
    TDateTime): String;
begin
  Result := Result + FormatUTF8('(%>="%" AND %<="%")',
    [aFieldName, DateToIso8601(aStartTime, True), aFieldName, DateTimeToIso8601(aEndTime, True)]);
end;

ab · 2019-02-27 07:52:16

Not exactly...

For safety and performance, always use bound parameters for your query, and DateTimeToSQL(), via e.g.

result := result + FormatUTF8(' %>? and %<=?', [aFieldName, aFieldName], [DateTimeToSQL(aStartTime), DateTimeToSQL(aEndTime)]);

or

result := FormatUTF8('% %>? and %<=?', [result, aFieldName, aFieldName], [DateTimeToSQL(aStartTime), DateTimeToSQL(aEndTime)]);

See the documentation at https://synopse.info/files/html/Synopse … ml#TITL_36

edwinsn · 2019-02-27 10:19:25

Oh yes, did a code search and found that I also use that approach you illustrated

anuchit · 2019-03-04 07:18:31

Thank you so much

mORMot Open Source

#1 2019-02-27 02:44:41

Filter Date Data

#2 2019-02-27 04:28:13

Re: Filter Date Data

#3 2019-02-27 07:52:16

Re: Filter Date Data

#4 2019-02-27 10:19:25

Re: Filter Date Data

#5 2019-03-04 07:18:31

Re: Filter Date Data

Board footer