cast column to_date with multiple date formats in original text column in postgresql

user_3517025 0
Viewed 555

I have a table with a event_date text column with values following one of two date formats:

  1. 'YYYYMMDD'
  2. 'YYYY-MM-DD'

For example:

event_date: text
1991-04-01
2009-02-11
20010101
NULL
20020101

How might I parse that column into Date format?

Especially considering that TO_DATE() will take only one possible format

date-conversion date postgresql
1 Answers

Both formats would be converted to dates with:

SELECT event_date::date event_date
FROM tablename;

Or use a CASE expression to choose one of the two formats:

SELECT TO_DATE(
         event_date,
         CASE
           WHEN event_date LIKE '____-__-__' THEN 'YYYY-MM-DD'
           WHEN event_date LIKE '________' THEN 'YYYYMMDD'
         END
       ) event_date
FROM tablename;

See the demo.

user_104988280
Related