Recent

Author Topic: Sqlite and date range query  (Read 3268 times)

WibblyTim

  • New Member
  • *
  • Posts: 46
    • http://www.wibblytim.co.uk
Sqlite and date range query
« on: January 20, 2017, 07:11:25 pm »
Hi all,

I'm using the SQLdb components.

I'm having a problem with a query, I cannot get it to work in the way that I think it should!
I'm attempting to display records in a DBGrid that have a date of a calendar month (January, February etc) but, I think I'm missing something and I don't know what. I'm hoping a fresh pair of eyes will be able to highlight my error.

Code here,
m := month as integer, y := year as integer;
Code: Pascal  [Select][+][-]
  1.  endDate := EndOfAMonth(y, m);
  2.   startDate := EncodeDate(y, m, 1);
  3.    DecodeDate(startDate, myYear, myMonth, myDay);
  4.    sd := IntToStr(y) + '-' + IntToStr(myMonth) + '-' + IntToStr(myDay);
  5.    DecodeDate(endDate, myYear, myMonth, myDay);
  6.    ed := IntToStr(y) + '-' + IntToStr(myMonth) + '-' + IntToStr(myDay);
  7.  ShowMessage(sd);
  8.  ShowMessage(ed);
  9.  SQLQuery.Close;
  10.  SQLQuery.SQL.Text:='SELECT * FROM entry WHERE date BETWEEN ' + sd + ' AND ' + ed;
  11.  SQLTransaction.Active := True;
  12.   SQLQuery.Open;                    

The result of this should display only records that have dates within a calendar month. If I substitute "2017-1-31" instead of ed for example for January then the results are correct.

Can someone help me please?

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Sqlite and date range query
« Reply #1 on: January 20, 2017, 07:16:12 pm »
My guess would be the dates are not actually in quotes when you build the query. I suggest using prepared / paramater queries to avoid the need for formatting.

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: Sqlite and date range query
« Reply #2 on: January 21, 2017, 08:36:17 am »
Right.
Either use:
  sd := QuotedStr(IntToStr(y) + '-' + IntToStr(myMonth) + '-' + IntToStr(myDay));
or
  SQLQuery.SQL.Text:='SELECT * FROM entry WHERE date BETWEEN :sd AND :ed';
  SQLQuery.ParamByName('sd').AsDate := startDate;
  SQLQuery.ParamByName('ed').AsDate := endDate;

WibblyTim

  • New Member
  • *
  • Posts: 46
    • http://www.wibblytim.co.uk
Re: Sqlite and date range query
« Reply #3 on: January 22, 2017, 06:33:31 pm »
goodname and Lacak, many thanks for your replies to my cry for help. Unfortunately, despite my attempting every variation of code I can devise I cannot seem to crack this problem. Most frustrating!

I have altered the record structure to post the date as a string instead of a Date  but this also doesn't work.

I will have to have a think and come at the problem from another angle.

Once again, thank you for your responses.

 

TinyPortal © 2005-2018