How do I reference sqlite db column to use in update statement

Question

I am trying to update a datetime column in an android sqlite db to use international date format (yyyy-mm-dd) instead of the current format (mm/dd/yyyy). I want to use the sqlite date() function to reformat the current value of the column. I thought it would be as simple as the following:

update tblename set thedate = date(thedate)

but the above does not work.

How would i write the sql statement to accomplish this?

thanks patrick

Answer 1

DATE() doesn't understand your old date format.

The following should work:

UPDATE tblname SET thedate = substr(thedate, 7, 4) || '-' || substr(thedate, 1, 2) || '-' || substr(thedate, 4, 2);