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

回答1:

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.

回答2:

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\'

回答3:

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.

回答4:

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\')

回答5:

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.

回答6:

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\')