SQL Select between dates

Question

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 but appreciate any help I can find   Thanks

User · Accepted 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

User · Answer

Special thanks to Jeff and vapcguy your interactivity is really encouraging   Here is a more complex statement that is useful when the length between     is unknown     SELECT   FROM tableName WHERE julianday      substr substr date  instr date       1   instr substr date  instr date       1        1     -        case when length      substr date  instr date       1  instr substr date  instr date       1      -1        2     then     substr date  instr date       1  instr substr date  instr date       1       -1      else      0   substr date  instr date       1  instr substr date  instr date       1       -1      end    -        case when length substr date 1  instr date      -1     2     then substr date 1  instr date      -1       else      0   substr date 1  instr date      -1       end   BETWEEN julianday  2015-03-14   AND julianday  2015-03-16

User · Answer

One more way to select between dates in SQLite is to use the powerful strftime function   SELECT   FROM test WHERE strftime   Y- m- d   date  BETWEEN  11-01-2011  AND  11-08-2011    These are equivalent according to https   sqlite org lang datefunc html   date       strftime   Y- m- d          but if you want more choice  you have it

User · Answer

Or you can cast your string to Date format with date function  Even the date is stored as TEXT in the DB  Like this  the most workable variant    SELECT   FROM test WHERE date date   BETWEEN date  2011-01-11   AND date  2011-8-11

User · Answer

SELECT   FROM TableName WHERE julianday substr date 7    -   substr date 4 2    -   substr date 1 2   BETWEEN julianday  2011-01-11   AND julianday  2011-08-11     Note that I use the format   dd mm yyyy  If you use d m yyyy  Change in substr    Hope this will help you

User · Answer

Change your data to that formats to use sqlite datetime formats   YYYY-MM-DD YYYY-MM-DD HH MM YYYY-MM-DD HH MM SS YYYY-MM-DD HH MM SS SSS YYYY-MM-DDTHH MM YYYY-MM-DDTHH MM SS YYYY-MM-DDTHH MM SS SSS HH MM HH MM SS HH MM SS SSS now DDDDDDDDDD  SELECT   FROM test WHERE date BETWEEN  2011-01-11  AND  2011-08-11

[sql] SQL Select between dates

Examples related to sql

Examples related to sqlite