Recent

Author Topic: Can't Search on Date Field in SQLite  (Read 6787 times)

mrmikehicks

  • New Member
  • *
  • Posts: 39
Can't Search on Date Field in SQLite
« on: October 28, 2009, 01:22:15 am »
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

  • Sr. Member
  • ****
  • Posts: 457
Re: Can't Search on Date Field in SQLite
« Reply #1 on: October 28, 2009, 10:40:42 am »
Sqlite3Dataset stores date as it's float representation.

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

davesimplewear

  • Sr. Member
  • ****
  • Posts: 319
    • Davids Freeware
Re: Can't Search on Date Field in SQLite
« Reply #2 on: October 28, 2009, 12:47:35 pm »
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.
All things considered insanity seems the best option

mrmikehicks

  • New Member
  • *
  • Posts: 39
Re: Can't Search on Date Field in SQLite
« Reply #3 on: October 28, 2009, 06:29:07 pm »
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

 

TinyPortal © 2005-2018