On Query locate on dates column

[eldonfsr]

How i can locate values on columns type date, because the date must be complete '02/28/2022'  or partial date '02' , could be possible.. on dates fields look doesn't work on string work fine...

Code: Pascal  [Select][+][-]

1. procedure TFormMain.DBGInvoKeyPress(Sender: TObject; var Key: char);
2. Var ColName:String;
3.     DSearch:TDateTime;
4. begin
5.   ColName:=DBGInvo.Columns.Items[DBGInvo.SelectedIndex].FieldName;
6.  if( (key >=#40) and (key <=#125)) then begin
7.     if(DBGInvo.Columns.Items[DBGInvo.SelectedIndex].Field.DataType <> ftDate ) then begin
8.       search +=key;
9.       with DBGInvo do begin
10.         DBGInvo.DataSource.DataSet.Locate(colname,search,[loCaseInsensitive,LoPartialKey]);
11.       end;
12.     end else begin
13. //      DSearch:=Now;
14.       search += key;
15. //      dsearch := CreateNewDate(key);
16.       with DBGInvo do begin
17.          DBGInvo.DataSource.DataSet.Locate(colname,'02/28/2022',[loCaseInsensitive,LoPartialKey]);
18.        end;
19.  
20.     end;
21.  //   showmessage(search);
22.     key:=#0;
23.   end;
24.   if( Key=#27) then begin
25.      search :='';
26.   end;
27.  
28. end;
29.  
30.  

[Thaddy]

The database engine you use has possibly a REAL datetime storage (not all have) and that storage is basically in yyyymmdd hhmmssms order - simplified.
Now you have multiple options:
1. sort on the datetime which is usually a double and fast
2. use decodedatetime and sort as string, which is slower.

[eldonfsr]

Ok Thanks let me tried what is best option...

[Thaddy]

If your database engine has a real datetime field that is always the best option. And store date and time otherwise always in yyyymmdd format and in UTC.

ddmmyyyy is ONLY for presentation, not for computing. Guess, what? it is in the wrong order for efficient sorting.....

[eldonfsr]

Well i try those
    DSearch:TDateTime;
    dsearch:= StrToDate('02/28/2022');
    with DBGInvo do begin
         DBGInvo.DataSource.DataSet.Locate(colname, now ,[loCaseInsensitive,LoPartialKey]);
      //here using the current date but doesn't work also with button line change dserach
     DBGInvo.DataSource.DataSet.Locate(colname, dsearch ,[loCaseInsensitive,LoPartialKey]);
//   doesn't work too..
     end; 

thanks for your time and help me too focus on right directions..

[Zvoni]

it helps reading the Documentation: https://www.freepascal.org/docs-html/fcl/db/tlocateoptions.html

The LocateOptions are for Strings. How is CaseInsensitive and PartialKey supposed to work on numeric Data?

That said: For "locating" Dates the way you want, there is only the way to use Strings, since locate on Non-Strings always looks for an EXACT match.
If your DateTime as a REAL looks like "47689.2587" you will never locate it on a partial input

If your DateTime-Column is a REAL (Floating point type), additionally ship a String-Representation of your DateTime-column as a result of your Query, and do the Locate on that.
Notabene: Don't blame me (or anyone else) if the search is dog-slow....

EDIT: Something else:
Your line "search += key;" in Line 8 and 14 are duplicates. Set it once outside between Line 6 and 7.
It doesn't matter which result you get on the If-Condition in Line 7, you set "search += key;" in either case. So it's a duplicate

Next: In Line 9 and 16 you start a With block, but in the following line you fully qualify your Object again.
Either remove the With-Blocks or leave out your Object in the following line