How to convert a date of the format 'April 9, 2013' into the format 'yyyy-mm-dd' in SQLITE

vthapa 0
Viewed 45

I have been trying to convert a column consisting of dates in the format 'April 9, 2013' into the format 'yyyy-mm-dd' which would result in '2013-04-09' in SQLite.

I have tried using the 'date' & 'strftime' function but get null as a result

Can anyone help me out on this?

dataformat sqlite
1 Answers

If your dates always are in the form of Monthname[space]day[comma]year this should work. Assuming your field name is dt and table name test here, change as needed.

SELECT
        SUBSTR(dt, instr(dt, ',') + 2) || '-' ||
        printf('%02d',
         CASE substr(dt, 0, instr(dt, ' '))
             WHEN 'January' THEN 1
             WHEN 'February' THEN 2
             WHEN 'March' THEN 3
             WHEN 'April' THEN 4
             WHEN 'May' THEN 5
             WHEN 'June' THEN 6
             WHEN 'July' THEN 7
             WHEN 'August' THEN 8
             WHEN 'September' THEN 9
             WHEN 'October' THEN 10
             WHEN 'November' THEN 11
             WHEN 'December' THEN 12
          END)  || '-' ||
        printf('%02d',
        substr(
         substr(dt, 0, instr(dt, ',')),
         instr(substr(dt, 0, instr(dt, ',')), ' ')+1
         )) as mydate
FROM test

+------------+
|   mydate   |
+------------+
| 2013-04-09 |
+------------+
MyICQ0
Related