Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 973
  • Last Modified:

Filter on dates with TQuery and Filter property

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
dmorin
Asked:
dmorin
1 Solution
 
slautinCommented:
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
 
dmorinAuthor Commented:
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
 
rwilson032697Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slautinCommented:
OK!
Use FormatDateTime('dd.mm.yyyy hh:mm:ss',ddate);
for example...
I test it!
But I recommend use ParamByName procedure.
0
 
slautinCommented:
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
 
slautinCommented:
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
 
dmorinAuthor Commented:
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
 
dwwangCommented:
May I know the DBMS you are using, and the type of that date field defined in that DBMS?
0
 
BlackManCommented:
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
 
slautinCommented:
What SQL Server are you use?
Can you type more sample of code?
0
 
slautinCommented:
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
 
dmorinAuthor Commented:
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
 
slautinCommented:
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
 
slautinCommented:
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
 
dmorinAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now