On Query locate on dates column
lazarus / 16-Databases

----------------------------------------------------------------------
eldonfsr · 2024-02-03 19:57

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TFormMain.DBGInvoKeyPress(Sender: TObject; var Key: char);Var
ColName:String;    DSearch:TDateTime;begin 
ColName:=DBGInvo.Columns.Items[DBGInvo.SelectedIndex].FieldName; if(
(key >=#40) and (key <=#125)) then begin   
if(DBGInvo.Columns.Items[DBGInvo.SelectedIndex].Field.DataType <>
ftDate ) then begin      search +=key;      with DBGInvo do begin    
  DBGInvo.DataSource.DataSet.Locate(colname,search,[loCaseInsensitive,
LoPartialKey]);      end;    end else begin//      DSearch:=Now;     
search += key;//      dsearch := CreateNewDate(key);      with DBGInvo
do begin         DBGInvo.DataSource.DataSet.Locate(colname,'02/28/2022
',[loCaseInsensitive,LoPartialKey]);       end;     end; //  
showmessage(search);    key:=#0;  end;  if( Key=#27) then begin   
 search :='';  end; end;

----------------------------------------------------------------------
Thaddy · 2024-02-03 20:43

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 · 2024-02-03 21:20

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

----------------------------------------------------------------------
Thaddy · 2024-02-03 21:53

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 · 2024-02-04 00:38

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 · 2024-02-05 08:05

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