sqlite convert 'dd.MM.yyyy' formated String to date

Question

i have a sqlite database on my android, whith a datetime column, that contains a date with the Format dd.MM.yyyy. It's not my Database, I'm niot able to change the Dateformat. I want to compare the date in the database with a String, which is representing a secon date, but everything I tryed failed. How can I convert this column to a valid, compareable date? date(), dattime() sdfttime() everything returns NULL.

Answer 1

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

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

Problem Example:

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

Solution Example:

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

Result Example:

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

Thank you all!

Other posts inspected:

1. Sqlite convert string to date
2. SOLite:Date formatter in SQLite
3. How can I convert datetime to date format in SQLite?
4. Sqlite convert string to date
5. How to convert a DD-MM-YYYY date format string into a YYYY-MM-DD date format string or into a NSDate object in Objective-C?

Answer 2

Try this query to change the column to the proper format for a text-date (assume table named table and column named date):

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

You can also turn this around into a where clause per this answer.

Answer 3

Do you want do this in the database (SQL) or in program code? In Java you may use SimpleDateFormat

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