Can't Search on Date Field in SQLite
lazarus / 16-Databases

----------------------------------------------------------------------
mrmikehicks · 2009-10-28 00:22

I have an SQLite table containing 4 items: ID, ITEM, DESC, DATE

I am using the sqlite3dataset component and a datasource. I also have
a dbgrid that is using the datasourse and sqlite3dataset components.
Normally the query for this table is set to 'select * from item' which
contains all the records. Three fields in the database have a 'search'
button to allow searching on the record set. For the DATE 'search'
button  I use a calendar dialog to select a date and then I set a
dbquerry with the results of the calendar dialog to search for records
entered on that date. Here's (I hope) enough code for all to see
what's going on:
...
var
  main_form: Tmain_form;
  search_time_edit: string;
...
procedure Tmain_form.Button10Click(Sender: TObject);
var found_it: boolean;
var tstr: string;
begin
   if CalendarDialog1.Execute then
      search_time_edit := datetostr(CalendarDialog1.Date);
   found_it := false;
   tstr := 'SELECT * FROM ITEM WHERE ITEM.DATE = ' + '''' +
search_time_edit + '''' + ';';
   dmr.sqlite_ds_item1.close;
   dmr.sqlite_ds_item1.SQL := tstr;
   dmr.sqlite_ds_item1.open;
   try
     if Form2.ShowModal = mrOk then begin

when the grid opens, it either displays all records or none.
I started off with 'select * from item where date = tstr'
Then I tried adding quotes to the date, and it still doesn't work.
I don't understand why it is so difficult to search a date field for a
given date. All help is appreciated.
     Thanks

----------------------------------------------------------------------
LuizAmérico · 2009-10-28 09:40

Sqlite3Dataset stores date as it's float representation.

Try the following: search_time_edit :=
FloatToStr(CalendarDialog1.Date);

----------------------------------------------------------------------
davesimplewear · 2009-10-28 11:47

I have found the SQL function in sqlite very poor so I tend to use the
exesql(select statement) and exesql cmnd to do queries.

----------------------------------------------------------------------
mrmikehicks · 2009-10-28 17:29

Thank You so much, I didn't see that in the sqlite docs, but I suppose
you'd have to be looking explicitly for floating point representation,
and I wasn't. Anyway thanks very much. Now I can move on to my next
big adventure.
     Mike