Geek Answers Handbook

Sqlite convert 'dd.MM.yyyy' formed String to date

I have a sqlite database on my android, with a datetime column that contains a date with the format dd.MM.yyyy. This is not my database, I cannot change the Dateformat. I want to compare the date in the database with String, which represents the sent date, but everything I tried failed. How can I convert this column to a valid, comparable date? date (), dattime () sdfttime () all returns NULL.

+4
source share
3 answers

I searched on google for "sqlite3 date ddmmyyyy" and this post is the best solution (and the only thing that worked for me among the posts listed below):

Problem: SQLite table contains dates in DD/MM/YYYY format and must be converted to SQLite native format YYYY-MM-DD

Example problem:

 sqlite> select id, calendar_day from tbl1; id;calendar_day 4248281;2011-06-19 4248282;2011-06-19 4248283;19/06/2011 4248284;19/06/2011

Example solution:

 sqlite> update tbl1 set calendar_day = substr(calendar_day, 7) || "-" || substr(calendar_day,4,2) || "-" || substr(calendar_day, 1,2) where id>4248282;

Example result:

 sqlite> select id, calendar_day from tbl1; id;calendar_day 4248281;2011-06-19 4248282;2011-06-19 4248283;2011-06-19 4248284;2011-06-19

Thanks everyone!

Other verified posts:

+6
source

Do you want to do this in a database (SQL) or in program code? In Java you can use SimpleDateFormat

 SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy"); Date d = sdf.parse("21.03.1997");
+2
source

Try this query to change the column to the appropriate format for the text date (suppose a table named table and a column named date ):

 update table set date = substr(date, 7) || "-" || substr(date,4,2) || "-" || substr(date, 1,2);

You can also include this in the where clause for this answer .

+1
source

More articles:


All Articles