Sqlite3 conversion MM/DD/YY string to YYYY-MM-DD

Question

I am trying to convert a string column of existing values from 'MM/DD/YY' to 'YYYY/MM/DD'.

Example Data:

Table: backlog_backlogData

[LoadDate]
06/29/18
06/29/18
06/28/18
07/24/18
07/24/18

I have tried this (in SQLite Manager FireFox extention):

UPDATE backlog_backlogData SET LoadDate = substr(LoadDate, 7, 2)||”-“||substr(LoadDate, 1,2)||”-“||substr(LoadDate, 4,2)

Unfortunately, the column is updated with only the first substr (results: 18). It seems like SQLite does not like concatenates ||?

I've searched for an answer and have not found anything that works.

Thanks for you time.

score 0 · Answer 1 · answered Aug 20 '18 at 17:30

0

you can check the query with a SELECT statement

  SELECT substr(LoadDate, 7, 2)||"-"||
         substr(LoadDate, 1,2)||"-"||
         substr(LoadDate, 4,2) 
     from  backlog_backlogData

answered Aug 20 '18 at 17:30

Ryan Z

With SELECT, I get no such row LoadDate. I appreciate the python method to convert though. I'll look into that more. – Prouderthings Aug 20 '18 at 19:46

score 0 · Accepted Answer · answered Aug 20 '18 at 19:58

I'm confident it was an issue with the SQLite Manager Firefox Extension. I ran the following code from cursor in python and it worked:

UPDATE backlog_backlogData SET LoadDate = substr(LoadDate, 7, 2) || '-' || substr(LoadDate, 1,2) || '-' || substr(LoadDate, 4,2)

I then reset my data and reran the code within the SQLite Manager Extension and it only processed the code up to the first concat.

EDIT: Thanks for the help below!

2 Answers2