Update date sting in column-CodePudding

Table:

id | time
--------------------
1  | 12.09.2022 13:01
2  | 12.09.2022 13:02
3  | 12.09.2022 13:03
4  | 12.09.2022 13:04

The time column is of type VARCHAR.

I need to convert from 12.09.2022 13:01 to 2022-9-12 13:01

Can I do this with SQL like UPDATE query?

PostgreSQL

CodePudding user response：

To fix this issue once and for all, change the datatype:

ALTER TABLE foo
    ALTER COLUMN time SET data type TIMESTAMP USING(to_timestamp(time, 'DD.MM.YYYY HH24:MI'))

to_timestamp(time, 'DD.MM.YYYY HH24:MI') will convert the varchar to a proper timestamp.

And of course you should also fix your input, somebody did a nasty INSERT on this table...