SQLLite between dates

I found a similar question on the forum but it did not solve my problem…
I always get the wrong date range, regardless what i try.

I tried to store the date field as text and as SQLDATE in the database.

sql="SELECT Field1, Field2, Date FROM Tablename WHERE Date Between  '" + startdate.Text + "' And   '" + enddate.Text + "'"

Example;
there are two invoice in the database.

  1. Invoice one with date 01.01.2013
  2. invoice two with date 01.02.2013
    A selection between 01.01.2013 and 31.01.2013 will also display the second invoice

Any idea what migh tbe wrong ?
Is it the date format wich leads to wrong results ?

Thanks in advance !

SQLite is not very flexible with date fields. Try this using SQLDate (or SQLDateTime):

"INSERT INTO Tablename (Field1, Field2, Date) VALUES ('Field1', 'Field2', '" + date.SQLDate + "');"

sql="SELECT Field1, Field2, Date FROM Tablename WHERE Date Between '" + startdate.SQLDate + "' And '" + enddate.SQLDate + "';"

If you work with SQLDate(Time), you have to make sure in the first place, that you hand over your Date(Time) values in the SQLDate(Time) Format, which is YEAR-MONTH-DAY HOUR:MINUTES:SECONDS.

Xojo can help you with the translation of your TextField Date Strings, by using ParseDate:
To parse the startDate for example, you would do something like this:

theTrueBool=ParseDate(startdate.Text) If theTrueBool Then startdate.Text = theDate.SQLDateTime End If

But please mind that ParseDate uses regional/international system date and time format settings on the user’s computer.

Try to use .TotalSeconds and greater or equal to statements in your SQL Query instead ‘between’.

Parsedate is the solution.

Many thanks,
Wolfgang

You are welcome. Glad i could help.