SQLite Probs

Feb 4, 2013 at 11:04am
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:

1
2
3

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


Thanks for any help,

Jon
Feb 4, 2013 at 1:26pm
but if Date1: 30/01/2013 I get Null.
Have you tried 01/30/2013?
Feb 4, 2013 at 2:16pm
Yes, it was in that format originally and I tried swapping to 30/01/2013 but will double-check. Thanks for the reply
Last edited on Feb 4, 2013 at 2:16pm
Feb 4, 2013 at 3:40pm
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".
Last edited on Feb 4, 2013 at 3:40pm
Feb 4, 2013 at 4:02pm
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 :-(
Feb 4, 2013 at 4:39pm
YYYY-MM-DD
Feb 4, 2013 at 5:30pm
closed account (iw0XoG1T)
http://www.sqlite.org/lang_datefunc.html
Topic archived. No new replies allowed.