?
Solved

Filter on dates with TQuery and Filter property

Posted on 1998-12-28
15
Medium Priority
?
926 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

771 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