SQLite, making a query based on Dates

Question

I've been searching on how to do this but am not coming up with a clear answer (maybe my resources are not that good). I need to make a SQLite query to retrieve data between two dates from a SQLite DB.

I am trying this:

SELECT CONTACTNAME
FROM SHIPMENT 
WHERE DATECREATED 
BETWEEN date('11-15-2010') 
AND date('12-25-2010');

The data is in my SQLite DB as:

CONTACTNAME=VARCHAR
DATECREATED=date (format: "11/22/2010")

Answer 1

SQLite does not have a date type. You can get by with storing dates as strings, but you need to use YYYY-MM-DD date order, NOT MM-DD-YYYY. There are two major reasons for this:

(1) Correct ordering

With YYYY-MM-DD dates, the lexicographical order is the same as the chronological order, which makes the <, >, and BETWEEN operators work as expected.

This is not the case for MM-DD-YYYY dates. A query for dates BETWEEN '11-15-2010' AND '12-25-2010' will falsely match '11-22-1963' and '12-21-2012'.

(2) Compatibility with SQLite date and time functions.

They accept both string and numeric (Julian) dates, but if you pass a string to any of these functions, it has to have YYYY-MM-DD order.

Answer 2

Try just removing date():

SELECT CONTACTNAME
FROM SHIPMENT 
WHERE DATECREATED 
BETWEEN '2010-11-15' 
AND '2010-12-25'