SQL 在日期之间选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8187288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Select between dates
提问by Zombian
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:
我正在运行 sqlite 来选择销售报告的两个范围之间的数据。要从两个日期之间选择数据，我使用以下语句：
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!
该语句获取所有日期，甚至是那些超出标准的日期。您看到输入的日期格式与我返回的格式相同。我不确定出了什么问题，但感谢我能找到的任何帮助。谢谢！
回答by Eric Petroelje
SQLLite requires dates to be in YYYY-MM-DDformat. 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.
SQLLite 要求日期为YYYY-MM-DD格式。由于数据库中的数据和查询中的字符串不是这种格式，因此它可能将您的“日期”视为字符串。
回答by Utku Y?ld?r?m
Change your data to that formats to use sqlite datetime formats.
将您的数据更改为该格式以使用sqlite 日期时间格式。
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'
回答by AFD
One more way to select between dates in SQLite is to use the powerful strftimefunction:
在 SQLite 中选择日期的另一种方法是使用强大的strftime函数：
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:
根据https://sqlite.org/lang_datefunc.html这些是等效的：
date(...)
strftime('%Y-%m-%d', ...)
but if you want more choice, you have it.
但如果你想要更多的选择，你就有了。
回答by adudakov
Or you can cast your string to Date format with datefunction. 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')
回答by TROUZINE Abderrezaq
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
请注意，我使用的格式： dd/mm/yyyy
If you use d/m/yyyy, Change in substr()
如果您使用 d/m/yyyy，请更改 substr()
Hope this will help you.
希望这会帮助你。
回答by TROUZINE Abderrezaq
Special thanks to Jeffand vapcguyyour interactivity is really encouraging.
特别感谢Jeff和vapcguy，你们的互动真的很鼓舞人心。
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')
