Solved

Filter on dates with TQuery and Filter property

Posted on 1998-12-28
15
902 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 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