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:

How might I parse that column into Date format?

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

CodePudding user response：

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.