CODEWITHSUNDEEP
postgresql
Sumit Singh
Sumit Singh

Reputation: 129

Extracting dates from different formats in PostgreSQL

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')

enter image description here

Upvotes: 1

Views: 64

Answers (1)

clemens
clemens

Reputation: 17721

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.

Upvotes: 2

Related Questions