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?
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);