SQLite Probs

[JonB]

Hi all,
I have a simple table in SQLite:
Column 1: vistDate (stored as date)
Column 2: mileage

As a test, I created one row,
visitDate = 03/02/2013
Mileage = 10

I want to be able to add all the mileage between two dates but in my SQLite query, it returns zero mileage if the first date is not in the same month.

I am inputting the dates in the dd/mm/yyyy format above. It works fine for
Date 1: 01/02/2013
Date 2: 04/02/2013

i.e. 10 miles is returned as answer

but if Date1: 30/01/2013 I get Null.

Here's the query:

SQLiteExec("SELECT SUM(mileage) FROM mileageTable WHERE visitDate BETWEEN '01/02/2013' AND '04/02/2013')
 

Thanks for any help,

Jon

[kbw]

but if Date1: 30/01/2013 I get Null.

Have you tried 01/30/2013?

[JonB]

Yes, it was in that format originally and I tried swapping to 30/01/2013 but will double-check. Thanks for the reply

[helios]

I believe in SQLite 'date' is just a synonym for 'text', so BETWEEN '01/02/2013' AND '04/02/2013' is a lexicographical comparison, not a date comparison. As strings of text, "30/01/2013" > "04/02/2013".

[JonB]

Thanks Helios, that makes sense as to why I'm returning NULL.
How would I approach a solution...perhaps store the dates in unix timestamp format? what a drag :-(

[ne555]

YYYY-MM-DD

[chwsks]

http://www.sqlite.org/lang_datefunc.html