SQLite compare dates

Question

I have this SQL-Statement:

SELECT Geburtsdatum FROM Kunde
WHERE Geburtsdatum BETWEEN '1993-01-01' AND '2000-01-01'

but I get some weird results, like: 2.02.1990

'Geburtsdatum' is a DATE

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

Answer 1

From the documentation:

SQLite does not have a storage class set aside for storing dates and/or times.

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.

Going back up to section 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.

Good. So let's try:

SELECT Geburtsdatum FROM Kunde
WHERE Geburtsdatum 
    BETWEEN julianday('1993-01-01') AND julianday('2000-01-01'); 

---

Oddly enough, SQL Fiddle seems to store DATEs 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'); 

Answer 2

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

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.