SQLite比较日期 [英] SQLite compare dates
问题描述
我有这个SQL语句:
SELECT Geburtsdatum FROM Kunde
WHERE Geburtsdatum BETWEEN '1993-01-01' AND '2000-01-01'
但我得到一些奇怪的结果,如:2.02。 1990
but I get some weird results, like: 2.02.1990
'Geburtsdatum'是DATE
'Geburtsdatum' is a DATE
任何建议或解决方案?
我的表结构:
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) )
推荐答案
从文档:
SQLite没有存储日期和/或时间的存储类。
SQLite does not have a storage class set aside for storing dates and/or times.
所以你的列不是完全存储为一个日期。进一步阅读,我们了解到,指定为 DATE
的列实际上使用亲和力规则5存储为 NUMERIC
。
So your column isn't exactly stored as a date. Reading further, we learn that columns specifed as DATE
are actually stored as NUMERIC
using affinity rule 5.
回到第1.2节:
REAL作为朱利安天数,天数公元前4714年11月24日中午在格林威治根据可笑的公历。
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.
好。那么让我们试试吧:
Good. So let's try:
SELECT Geburtsdatum FROM Kunde
WHERE Geburtsdatum
BETWEEN julianday('1993-01-01') AND julianday('2000-01-01');
奇怪的是, SQL Fiddle 似乎将 DATE
s存储为字符串,并且上述不起作用。在这种情况下,应该:
Oddly enough, SQL Fiddle seems to store DATE
s as strings and the above doesn't work. In this case, the following should:
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');
这篇关于SQLite比较日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!