[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Filter on dates with TQuery and Filter property

Posted on 1998-12-28
15
Medium Priority
?
959 Views
Last Modified: 2012-08-14
Does anyone know how to filter a TQuery on a datefield using the filter property?  I can do "TheDate = '1998-12-25 08:08:00.000'" in ISQL and it works fine but if I use the same for the filter property it does not return the record.  I've tried all combinations of date formats like ShortDate, LongDate, LongTime, etc. to no avail.
0
Comment
Question by:dmorin
15 Comments
 
LVL 1

Expert Comment

by:slautin
ID: 1353360
For TTable component:

procedure TForm1.Table1FilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
begin
 
 if {Type your filter} then
     Accept := True
 else
     Accept := False;
end;

For TQuery use ParamByName method.
Example:

Qury1.SQL.Text := 'select * from projects where ord_date=:ddate';
Qury1.ParamByName('date').AsDateTime := ....;

For TDateTime values use
En(De)CodeDate(Time) functions;

0
 

Author Comment

by:dmorin
ID: 1353361
Sorry I wasn't more specific.  I must use a TQuery.  I do not want to use the OnFilterRecord event (OnFilterRecord filters each record of the result set while setting the Filter property changes the "where" portion of the select statement causing the server to do all the work).  The queries are complex, therefore I want to use the Filter property of the TQuery component and let Delphi handle changing the where clause.  It works fine with strings and numerics, just can't get dates to work.
0
 
LVL 12

Expert Comment

by:rwilson032697
ID: 1353362
Use DateTimeToStr to see what format your locale will convert a datetime to. Then put that string as the filter property.

Does this help?

Cheers,

Raymond.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:slautin
ID: 1353363
OK!
Use FormatDateTime('dd.mm.yyyy hh:mm:ss',ddate);
for example...
I test it!
But I recommend use ParamByName procedure.
0
 
LVL 1

Expert Comment

by:slautin
ID: 1353364
Excuse me, I didn't understud what of the propertues you can't use. Type some from your code. I offer work with datetime fields and havn't problems. I can't understend your problems.
Isn't it?
0
 
LVL 1

Expert Comment

by:slautin
ID: 1353365
I wrote small example and test filter - all rights!

unit Unit1;
interface

uses ....

type
  TForm1 = class(TForm)
    Table1: TTable;
    Table1ID: TIntegerField;
    Table1BOSS: TIntegerField;
    Table1DATE: TDateTimeField;
    Table1TARG: TStringField;
    Table1RESOL: TStringField;
    Table1MEET: TBooleanField;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Edit1: TEdit;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  end;

var
  Form1: TForm1;


Does this help?
implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
begin
Table1.Filter := 'Date='+#39+Edit1.Text+#39;
   //Edit1.Text ='21.05.1998 16:00:00';
end;

end.
0
 

Author Comment

by:dmorin
ID: 1353366
To answer rwilson's comment, I have used DateTimeToStr to put in the value but that did not work either.
For slautin's comment, the Filter accepts something like mydate = '1998-12-12 09:09:09.940' or mydate = '1998-12-12 09:09:09' but does not bring up the correct record as it does when I do the same thing in ISQL.  I've tried all the date formats there are.  The value is stored in the tables as a datetime in the format '1998-12-12 09:09:09.940'.
0
 
LVL 4

Expert Comment

by:dwwang
ID: 1353367
May I know the DBMS you are using, and the type of that date field defined in that DBMS?
0
 
LVL 7

Expert Comment

by:BlackMan
ID: 1353368
Be sure to include a double set of ' in your filterstring, so that a pair of 's are send to the server.. Like this
TQuery.filter:='MyDate=''1998-12-12 09:09:09''';
0
 
LVL 1

Expert Comment

by:slautin
ID: 1353369
What SQL Server are you use?
Can you type more sample of code?
0
 
LVL 1

Expert Comment

by:slautin
ID: 1353370
With what accuracy you wants to get a value of filter?
If seconds sufficiently that possible to use BEETWEN for SQL, and ">="... "<=" for the filter.
0
 

Author Comment

by:dmorin
ID: 1353371
I will try <= in the filter to see what happens.  I may be able to live with seconds but would rather have the exact time.  I'm usine Sybase 6.0 for the SQL Server and a datetime field.
0
 
LVL 1

Accepted Solution

by:
slautin earned 2000 total points
ID: 1353372
When building filter, Delphi calling StrToDateTime function (look RTL) for TimeStamp fields , but this function trim milliseconds!

So I see you have two ways:
1. Build you filter with DbiAddFilter, dbiActivateFilter,  
   dbiDeactivateFilter, dbiDropFilter.  
   For example - look bde.hlp or DB.PAS.
2. Next way - use onFilterRecord event with yourself writing  
   parser. Its not hard.

very, very simple parser example
only '<','>','=','AND','OR' operations:


..
var
  cstak,  //code stak
  vstak   //variable stak
      : array [1..256] of variant; //staks len
  cptr, vptr : integer; //staks pointers

procedure putcs (val : variant);
begin
  Inc(cptr);
  cstak[cptr] := val;
end;

function getcs : variant;
begin
  result := cstak[cptr];
  Dec(cptr);
end;

procedure putvs (val : variant);
begin
  Inc(vptr);
  vstak[vptr] := val;
end;

function getvs : variant;
begin
  result := cstak[vptr];
  Dec(vptr);
end;

procedure parse;
var
   val_a : variant;
   vt : integer;
   is_val : Boolean;
begin
while cptr >= 1 do
   begin
   is_val := true;
   val_a := getcs;
   if val_a = 'OR' then    
       begin
       is_val := false;
       putvc(getvc or getvc);
       end;
   if val_a = 'AND' then    
       begin
       is_val := false;
       putvc(getvc and getvc);
       end;
   if val_a = '>' then    
       begin
       is_val := false;
       putvc(getvc > getvc);
       end;
   if val_a = '<' then    
       begin
       is_val := false;
       putvc(getvc > getvc);
       end;
   if val_a = '<' then    
       begin
       is_val := false;
       putvc(getvc < getvc);
       end;
   if val_a = '=' then    
       begin
       is_val := false;
       putvc(getvc > getvc);
       end;
   if is_val then  
       putvc(val_a);
   end;
end;

.........

procedure MyQuyryFilterRecord(DataSet: TDataSet; var Accept: Boolean);
begin
    //Example of preparing of filter
    // "COST>5.25 AND TIME='12.12.12 00:23:34.987'"
    putcs(FieldByName('COST').Value);
    putcs(5.25);
    putcs('>');
    putcs(FieldByName('TIME').Value);
    putcs(StrToDateTime('12.12.12 00:23:34.987'));
    //in this place you can use any way for translate
    //your dateTime to float!!!
    putcs('AND');

    //Execute filter
    Parse;
    Accept := getvs; //last in stak.
end;

If you don't want fill stak manually you must write preprocessor for translating string filter to the stak.

For compound sentences needed to write more complex parser
(with left and right parents).

Of course you can write via OOP - very good, I can too.

I have strong skills in parsers. Can I help you?

MrXms!

P.S.: I'll back six days.
0
 
LVL 1

Expert Comment

by:slautin
ID: 1353373
It's me again.
1. I'm looking RTL source code and detect that if you don't want use DbiAddFilter, dbiActivateFilter, dbiDeactivateFilter, dbiDropFilter, you must rewrite TFilterExpr and TExprParser  classes.

2. If you have VCL source (DB.PAS file), then change
 PutConstDateTime function:

//In source code
function TFilterExpr.PutConstDateTime(const Value: Variant): Integer;
var
  DateTime: TDateTime;
  DateData: Double;
begin
  if VarType(Value) = varString then
    DateTime := StrToDateTime(string(TVarData(Value).VString)) else
    DateTime := VarToDateTime(Value);
  DateData := TimeStampToMSecs(DateTimeToTimeStamp(DateTime));
  Result := PutConstNode(fldTIMESTAMP, @DateData, 8);
end;

Instead of StrToDateTime functions it is necessary to create own handler and reconstruct VCL.

Remember that Delphi stores date and time values in the float type. The integral part is the number of days that have passed since 12/30/1899. The fractional part put the time of day.
24 hours it's 86'400'000 msec.
if you divide your time by this value, you get fractional part in datetime.

For example: '1998-12-12 09:09:09.940'
time - '09:09:09.940'=32'949'940 msec.
fractional part = 32'949'940/86'400'000 = 0.381365046

Following this rule it is necessary anew to create specified above function.
0
 

Author Comment

by:dmorin
ID: 1353374
I really don't want to mess with the vcl because I'm dealing with multiple programmers at multiple locations, but slautin's solution seems to be the best way to handle this for anyone in the future.  Thank you for all your suggestions and especially for delving into the vcl to find out exactly what Delphi was doing.  Great job!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month20 days, 1 hour left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question