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

anuchit
Member
Registered: 2018-05-02
Posts: 4

Filter Date Data

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

Offline

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

edwinsn
Member
Registered: 2010-07-02
Posts: 1,218

Re: Filter Date Data

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;

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,620
Website

Re: Filter Date Data

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

Offline

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

edwinsn
Member
Registered: 2010-07-02
Posts: 1,218

Re: Filter Date Data

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


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

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

anuchit
Member
Registered: 2018-05-02
Posts: 4

Re: Filter Date Data

Thank you so much

Offline

Board footer

Powered by FluxBB