I'm using sqlite3 library on python and have some strings on my sqlite DB as follows:

I would like to convert the date column as follows:

I got some hints from this link, but I wasn't able to use it..

Would anyone mind to provide some hints or guidance? Your help will be very appreciated. Thanks..!

CodePudding user response：

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 ints, passes these ints 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])