Solved

Filter on dates with TQuery and Filter property

Posted on 1998-12-28
15
889 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 500 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now