CODEWITHSUNDEEP
sqlpostgresql
dc.
dc.

Reputation: 1429

UPDATE each item in a column

I'm having trouble figuring out a solution to this. I have several tables in my database with attributes having a date type. However, in one of my tables I was not thinking during the design process so the attribute is not of date but is a varchar. The dates in the "incorrect" table is formatted as dd-MMM-yyyy whereas all of the other dates are formatted as yyyy-mm-dd.

How can I run through the "incorrect" column and do CAST(myDate AS date) on each mis-typed attribute?

Upvotes: 0

Views: 109

Answers (1)

Phil
Phil

Reputation: 164910

I'd migrate to a date column.

  1. Add a nullable date column
  2. Run an update query to set the value of each column to the result of your cast
  3. Drop the varchar column
  4. Refactor your code or rename the new column to the name of the old one

Upvotes: 1

Related Questions