Extracting dates from different formats in PostgreSQL

Question

I am trying to change the type of the data type (from VARCHAR to DATE in PostgreSQL).

Generally the below code works but as you can see there are two date types for cc_active_date. Is there a way to emulate try catch in such scenario so that if casting DD/MM/YYYY fails then it takes YYYY-MM-DD hh:mm:ss?

alter table credit_card.hsbc alter column cc_active_date type date
using to_date(cc_active_date, 'DD/MM/YYYY')

Seen that, unable to do it, can u give me a simple syntax for alter column? — Sumit Singh
– Sumit Singh, Commented Nov 25, 2019 at 4:22
Do you see the part that says "statements" after the BEGIN? I would imagine that's where your ALTER COLUMN goes. — Robert Harvey
– Robert Harvey, Commented Nov 25, 2019 at 4:23

clemens · Accepted Answer · 2019-11-25 06:21:16Z

2

DATE is very flexible in recognizing the date format, and it already masters your formats. With SET datestyle you can control it so that you don't need to distinguish between cases:

SET datestyle TO ISO, DMY;
ALTER TABLE credit_card.hsbc ALTER COLUMN cc_active_date type DATE
USING cc_active_date::DATE;

An alternative for unsupported formats is an if-condition:

ALTER TABLE credit_card.hsbc ALTER COLUMN cc_active_date type DATE
USING TO_DATE(cc_active_date, CASE WHEN cc_active_date ~ '^\d{2}/\d{2}/\d{4}$' THEN 
    'DD/MM/YYYY' ELSE 'YYYY-MM-DD hh:mm:ss' END);

But I would only use something like this if the simpler variant doesn't work.

edited Nov 25, 2019 at 6:21

answered Nov 25, 2019 at 6:10

clemens

17.9k12 gold badges52 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extracting dates from different formats in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related