SQL statement to convert date-time format in some columns

I have an SQLite datab开发者_C百科ase in which some columns are date-time strings with the format 1/2/2000 3:45:56 am.

Is there an SQL statement which can convert these values to strings with an ISO-8601-like format (2000-01-02 03:45:56)?

(One possible non-SQL way to do this would be to export the database as a CSV file, and use Python, with its csv and datetime modules, to parse the columns.)

SQL As Understood By SQLite - Date And Time Functions.

Converting the other way would be easy: You could use strftime. But this requires the date to be in ISO 8601 format (or a Julian or Unix date) to begin with.

You could do it in SQL with string manipulation, but it would be horribly complex. The easiest way would be to make a user-defined function.

def fix_timestamp(timestamp):
    return datetime.datetime.strptime(timestamp, '%m/%d/%Y %I:%M:%S %p') \
           .strftime('%Y-%m-%d %H:%M:%S')

db = sqlite3.connect('...')
db.create_function('FixTimestamp', 1, fix_timestamp)

Then you can do UPDATE TheTable SET TheColumn = FixTimestamp(TheColumn).