-4

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?

vthapa
  • 1
  • Read [this](https://meta.stackoverflow.com/questions/271055), show your schema CREATE, sample data via "INSERT", and your query. – PChemGuy Sep 11 '22 at 05:49
  • Similar question [has been asked before](https://stackoverflow.com/questions/12336565/how-to-convert-string-date-into-date-format-in-sqlite). Before a solution emerges, maybe you can use something from that. But your solution would probably be simpler using an external program to manipulate the data. SQLite does not have native functions you need. – MyICQ Sep 11 '22 at 07:58

1 Answers1

0

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 |
+------------+
MyICQ
  • 987
  • 1
  • 9
  • 25