I have a column containing dates in the format yyyy-mm-dd. Now I'm running a SELECT query that converts that date to dd/mm/yyyy using TO_CHAR(dob :: DATE, 'dd/mm/yyyy') AS dob which works just fine. Now the problem I've encountered is that there are some bad records in that column, below is a sample table with a good record and a bad record:
| id | dob |
|----|------------|
| 1 | 2019-12-31 | // this returns 31/12/2019
| 2 | 31-12-2019 | // BAD RECORD, this returns an error
|----|------------|
The error I get on id 2 is:
ERROR: date/time field value out of range: "31-12-2019"
HINT: Perhaps you need a different "datestyle" setting.
SQL state: 22008
What I'd like is to conditionally check if the TO_CHAR(dob :: DATE, 'dd/mm/yyyy') is okay otherwise just use the dob without conversion. Any way to pull that off?
I'm using Postges 12
I was able to solve this by creating a function similar to the selected answer in the link posted by @a_horse_with_no_name > update vachar column to date in postgreSQL . The function:
create or replace function format_date_or_return_string(str varchar)
returns text language plpgsql as $$
begin
return to_char(to_date(str, 'yyyy-mm-dd), 'dd-mm-yyyy');
exception
when others then return str;
end $$;
So a SELECT will format the good records as desired and ignore the bad records.
SELECT id, format_date_or_return_string(dob) from the sample data in the question will return:
| 1 | 31/12/2019 | // good record
| 2 | 31-12-2019 | // ignored bad record