SQL SQLite 比较日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13091000/
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
SQLite compare dates
提问by Christian 'fuzi' Orgler
I have this SQL-Statement:
我有这个 SQL 语句：
SELECT Geburtsdatum FROM Kunde
WHERE Geburtsdatum BETWEEN '1993-01-01' AND '2000-01-01'
but I get some weird results, like: 2.02.1990
但我得到了一些奇怪的结果，例如：2.02.1990
'Geburtsdatum' is a DATE
'Geburtsdatum' 是一个日期
Any suggestions or solutions?
任何建议或解决方案？
my table-structure:
我的表结构：
CREATE TABLE Kunde (
Kunde_ID INTEGER NOT NULL ,
Card INTEGER ,
Vorname VARCHAR(255) NOT NULL ,
Nachname VARCHAR(255) NOT NULL ,
Ort VARCHAR(255) NOT NULL ,
Strasse VARCHAR(255) NOT NULL ,
Postleitzahl VARCHAR(10) NOT NULL ,
Mail VARCHAR(255) ,
Telefonnummer VARCHAR(255) ,
Geburtsdatum DATE NOT NULL ,
Beitrittsdatum DATE NOT NULL ,
Geschlecht INTEGER NOT NULL ,
Land VARCHAR(255) NOT NULL DEFAULT '?sterreich' ,
Bankname VARCHAR(255) ,
Bankleitzahl VARCHAR(255) ,
Kontonummer VARCHAR(255) ,
GroupID INTEGER NOT NULL ,
Besucher INTEGER ,
Access BOOLEAN ,
image BLOB NULL ,
writeDate DATE ,
drinkAbo BOOLEAN ,
PRIMARY KEY (Kunde_ID) )
回答by lc.
From the documentation:
从文档：
SQLite does not have a storage class set aside for storing dates and/or times.
SQLite 没有专门用于存储日期和/或时间的存储类。
So your column isn't exactly stored as a date. Reading further, we learn that columns specifed as DATEare actually stored as NUMERICusing affinity rule 5.
因此，您的列并未完全存储为日期。进一步阅读，我们了解到指定为的列DATE实际上是NUMERIC使用关联规则 5存储的。
Going back up to section 1.2:
回到第 1.2 节：
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
REAL 作为儒略日数，根据预测公历，自公元前 4714 年 11 月 24 日中午在格林威治的天数。
Good. So let's try:
好的。所以让我们试试：
SELECT Geburtsdatum FROM Kunde
WHERE Geburtsdatum
BETWEEN julianday('1993-01-01') AND julianday('2000-01-01');
Oddly enough, SQL Fiddleseems to store DATEs as strings and the above doesn't work. In this case, the following should:
奇怪的是，SQL Fiddle似乎将DATEs存储为字符串，而上述方法不起作用。在这种情况下，应：
SELECT Geburtsdatum FROM Kunde
WHERE date(Geburtsdatum)
BETWEEN date('1993-01-01') AND date('2000-01-01');
Additionally, in your case you seem to be getting some strange (read: localized) format returned. I wonder if it really is a string in your case too, just with a different format. You could try:
此外，在您的情况下，您似乎返回了一些奇怪的（阅读：本地化）格式。我想知道在你的情况下它是否真的也是一个字符串，只是格式不同。你可以试试：
SELECT Geburtsdatum FROM Kunde
WHERE strftime('%d.%m.%Y', Geburtsdatum)
BETWEEN date('1993-01-01') AND date('2000-01-01');
回答by rizalp1
Someone had the same problem and got it resolved. (use datetime function before comparison)
有人遇到了同样的问题并得到了解决。（比较前使用日期时间函数）
See SQLite DateTime comparison
Excerpt:Following the datetime function and having a string format as YYYY-MM-DD HH:mm:ss i achieved good results as follows
摘录：遵循日期时间函数并使用字符串格式为 YYYY-MM-DD HH:mm:ss 我取得了如下良好的结果
select *
from table_1
where mydate >= Datetime('2009-11-13 00:00:00')
and mydate <= Datetime('2009-11-15 00:00:00')
--EDIT--
- 编辑 -
You are basically comparing strings. Which is why the unexpected behavior. Convert to datetime using the function right before comparison and it should work.
您基本上是在比较字符串。这就是意外行为的原因。在比较之前使用该函数转换为日期时间，它应该可以工作。
回答by CL.
To be able to compare dates, you must store them in one of SQLite's supported date formats, such as a JJJJ-MM-TTstring. Your dates are stored as strings in a localized date format which is not recognized by SQLite.
为了能够比较日期，您必须将它们存储在SQLite 支持的日期格式之一中，例如JJJJ-MM-TT字符串。您的日期以 SQLite 无法识别的本地化日期格式存储为字符串。
If the most significant field is not at the start of the string, string comparisons will not work for dates.
With the values currently in your table, you will not be able to do any comparisons.
如果最重要的字段不在字符串的开头，则字符串比较不适用于日期。
使用表中当前的值，您将无法进行任何比较。
