SQLite creating date-based query

Question

I was looking for how to do this, but I did not come up with a clear answer (maybe my resources are not so good). I need to make a SQLite query to retrieve data between two dates from SQLite DB.

I am trying to do this:

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

Data in my SQLite DB:

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

Answer 1

Try simply deleting the date ():

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

Answer 2

SQLite does not have a date type. You can go by storing dates as strings, but you need to specify the date YYYY-MM-DD, NOT MM-DD-YYYY. There are two main reasons for this:

(1) Correct ordering

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

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

(2) Compatible 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 must be of the order YYYY-MM-DD.

Answer 3

 SELECT field_1,field_2,date_field WHERE julianday(date_field) BETWEEN julianday('1998-01-01') and julianday('2008-01-01');