Convert a VARCHAR value to a formatted date, ignoring errors

Question

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

The bigger problem is that you are storing dates as text here. Your date of bith column needs to be cleaned up, but Postgres is not the best place to be doing that. — Tim Biegeleisen, Mar 20 '20 at 08:08
Yes, the initial design of the system required just text simply for display purposes. But later on, a feature was added where the users required the age to be determined from the dob. This was much later on — Clint_A, Mar 20 '20 at 08:11
You can define function for conversion that use the `try/catch` block and use it in the select statement — Renato, Mar 20 '20 at 08:19
https://stackoverflow.com/a/10307443 or https://stackoverflow.com/questions/32791975/ or https://stackoverflow.com/a/43761300 — , Mar 20 '20 at 08:25

score 1 · Answer 1 · answered Mar 20 '20 at 08:21

It's not a good practice to store dates as strings and your problem proves it.
If you are sure that all the values in this column that have the format YYYY-MM-DD are valid dates then use the operator LIKE:

CASE 
  WHEN dob LIKE '____-__-__' THEN TO_CHAR(dob::DATE, 'dd/mm/yyyy') 
  ELSE dob
END AS dob

Clint_A · Accepted Answer · 2020-03-22T11:29:02.933

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

Convert a VARCHAR value to a formatted date, ignoring errors

2 Answers2