It may be possible to do this using only SQL, but as the answer you linked shows, it will look very complicated. Assuming reasonable quantities of data, it's simpler to fetch the values into Python, reformat them, and update the database.
The work is done in this statement:
date(*map(int, row[0].split('.'))).strftime('%Y-%m-%d')
which splits each date string on '.'
, converts the parts to int
s, passes these int
s to the date
constructor and then formats the resulting date
instance as a YYYY-mm-dd string.
Here's a complete example:
from datetime import date
import sqlite3
with sqlite3.connect(':memory:') as conn:
conn.execute("""CREATE TABLE t (d TEXT)""")
conn.execute(
"""insert into t (d) values ('2022.7.1'), ('2022.7.11'), ('2022.10.1'), ('2022.10.21')"""
)
conn.commit()
rows = conn.execute("""SELECT d FROM t""").fetchall()
# Make a list of dicts containing the old value and the new value.
values = [
{
'old': row[0],
'new': date(*map(int, row[0].split('.'))).strftime('%Y-%m-%d'),
}
for row in rows
]
conn.executemany("""UPDATE t SET d = :new WHERE d = :old""", values)
conn.commit()
rows = conn.execute("""SELECT d FROM t""")
for row in rows:
print(row[0])