Convert string to date in SQLITE

Question

I can convert SQLITE date format to string of any format using strtime function i.e

select  strftime('%Y-%d-%m-%Y', '2012-09-13 12:44:22');

but I have String which is in dd-MM-yyyy format, How can I convert to yyyy-MM-dd format in sqlite so that I can use that in date comparison.

Possible duplicate of http://stackoverflow.com/questions/3968618/sqlite-convert-dd-mm-yyyy-formated-string-to-date — Avadhani Y, Mar 22 '13 at 05:58

score 10 · Accepted Answer · answered Mar 22 '13 at 06:19

10

Use something like this:

select datetime(substr(col, 7, 4) || '-' || substr(col, 4, 2) || '-' || substr(col, 1, 2)) from table;

where col is the column of table having the date in yyyy-MM-dd format

answered Mar 22 '13 at 06:19

Diego Torres Milano

65,697
9
111
134

score 2 · Answer 2 · answered Mar 22 '13 at 06:09

You can try this

String date = "23-03-2013";
try {
    SimpleDateFormat format = new SimpleDateFormat(""dd-MM-yyyy");

    SimpleDateFormat df2 = new SimpleDateFormat("yyyy-MM-dd");

   date = df2.format(format.parse(str));

} catch (java.text.ParseException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

score 1 · Answer 3 · answered Jul 07 '16 at 22:37

I had a similar issue, where the date string had been inconsistently entered and so the length of the months, days and years were not consistent. Here is what I did to convert strings inconsistently formatted as M/D/YYYY to dates consistently formatted to YYYY-MM-DD:

    SELECT "DISP DATE", DATE(year||'-'||month||'-'||day) as dt_frmtd 
    FROM 
    (
    SELECT *
, CASE WHEN LENGTH(substr("DISP DATE", 1, instr("DISP DATE",'/')-1)) = 2 
       THEN substr("DISP DATE", 1, instr("DISP DATE",'/')-1)
       ELSE '0'|| substr("DISP DATE", 1, instr("DISP DATE",'/')-1)
       END as month
, CASE WHEN LENGTH(substr(substr("DISP DATE", instr("DISP DATE",'/')+1), 1, instr(substr("DISP DATE", instr("DISP DATE",'/')+1),'/')-1)) = 2
       THEN substr(substr("DISP DATE", instr("DISP DATE",'/')+1), 1, instr(substr("DISP DATE", instr("DISP DATE",'/')+1),'/')-1)
       ELSE '0'|| substr(substr("DISP DATE", instr("DISP DATE",'/')+1), 1, instr(substr("DISP DATE", instr("DISP DATE",'/')+1),'/')-1)
       END AS day           
, CASE WHEN LENGTH(substr(substr("DISP DATE", instr("DISP DATE",'/')+1), instr(substr("DISP DATE", instr("DISP DATE",'/')+1),'/')+1)) = 4
       THEN substr(substr("DISP DATE", instr("DISP DATE",'/')+1), instr(substr("DISP DATE", instr("DISP DATE",'/')+1),'/')+1)
       ELSE '20'|| substr(substr("DISP DATE", instr("DISP DATE",'/')+1), instr(substr("DISP DATE", instr("DISP DATE",'/')+1),'/')+1)
       END AS year 
    FROM DISP 
    )

Convert string to date in SQLITE

3 Answers3