0

I have an old table containing dates stored as strings like '20191001'. I have created a new field of type DATE and I try to recover the old dates in the new field :

UPDATE extcg SET newdate=to_date(olddate,'yyyymmdd');

The problem is that some of these dates are invalid (20190231). I would like to skip the conversion for these dates and leave the new field NULL.

Is there a way to exclude these in a WHERE statement ?

  • 1
    see: stackoverflow.com/questions/5966274/… – Luuk Feb 28 at 17:29
  • Perfect answer, I wonder why I didn't find that question. Thank you. – JC Boggio Feb 28 at 17:31
  • i was using Google with postgresql to_date skip errors, and it was the first link.... 😀 – Luuk Feb 28 at 17:33
0

For those interested, here is a function that does the job :

CREATE FUNCTION to_date_or_null(ADate TEXT, AFormat TEXT) RETURNS DATE AS
$BODY$
BEGIN
    RETURN to_date(ADate,AFormat);
EXCEPTION
    WHEN others THEN RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql IMMUTABLE STRICT;

I used it this way :

UPDATE extcg SET newdate=to_date_or_null(olddate,'yyyymmdd');

And thanks to @Luuk for mastering Google searches ;)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.