SQLite Datenbank Datumsformat ändern

[jobo]

Ich glaub, Du kannst einfach mit date() arbeiten. Zuvor musst Du das Textdatum mit substr umbauen in ein Format, das Date() versteht, z.B. YYYY-MM-DD

Beispiel:

Code:

sqlite> create table datest (mydate date);
sqlite> select * from datest
   ...> ;
sqlite> insert into datest values(date());
sqlite> select * from datest;
2011-01-06
sqlite> insert into datest
   ...> select date(
   ...>          substr("18/02/1999",-4,4) || "-" ||
   ...>          (CASE WHEN substr("18/02/1999",-7,1) = '/'
   ...>                THEN "0" || substr("18/02/1999",-6,1)
   ...>                ELSE substr("18/02/1999",-7,2)
   ...>                END) || "-"
   ...>             ||
   ...>          (CASE WHEN substr("18/02/1999",2,1) = '/'
   ...>                THEN "0" || substr("18/02/1999",1,1)
   ...>                ELSE substr("18/02/1999",1,2)
   ...>                END)
   ...>              );
sqlite> select * from datest;
2011-01-06
1999-02-18
sqlite> select min(mydate), max(mydate) from datest;
1999-02-18|2011-01-06
sqlite> select max(mydate)-min(mydate) from datest;
12
sqlite> select julianday(max(mydate))-julianday(min(mydate)) from datest;
4340.0
sqlite>