SQL Select between dates

I am running sqlite to select data between two ranges for a sales report. To select the data from between two dates I use the following statement:

SELECT * FROM test WHERE date BETWEEN "11/1/2011" AND "11/8/2011";

This statement grabs all the dates even those outside the criteria. The date format you see entered is in the same format that I get back. I’m not sure what’s wrong.

Answer

SQLLite requires dates to be in YYYY-MM-DD format. Since the data in your database and the string in your query isn’t in that format, it is probably treating your “dates” as strings.