Convert varchar into date PostgreSQL

Question

I need to convert dates of birth from type varchar into date. The table is called "customer" and the column is called "date_of_birth".

The Column has entries where the format of the date is dd.MM.YYYY and dd/MM/YYYY and I want to change every entry of the column.

The code I have right now is:

ALTER COLUMN date_of_birth type date USING to_date(date_of_birth, 'dd.MM.YYYY');

This code would work if the existing entries would all have the format "dd.MM.YYYY. Can someone help me adding the last piece of code, which also uses the other date format that I've mentioned above (dd/MM/YYYY).

I thank you for your time!

user330315 · Accepted Answer · 2020-06-25 07:32:33Z

1

If you only need to deal with those two formats mentioned, you can use a CASE expression:

ALTER TABLE your_table
  ALTER COLUMN date_of_birth type date 
    USING case 
            when date_of_birth like '%/%' then to_date(date_of_birth, 'dd/MM/YYYY')
            else to_date(date_of_birth, 'dd.MM.YYYY')
          end;

If you have more then those two formats, you need to extend the tests to find out which format each value uses.

answered Jun 25, 2020 at 7:32

user330315

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

Comments

Laurenz Albe · Accepted Answer · 2020-06-25 06:50:20Z

1

If you set datestyle appropriately, the type input function will do that automatically:

SET datestyle = ISO, DMY;

ALTER TABLE xxx
   ALTER date_of_birth TYPE date;

answered Jun 25, 2020 at 6:50

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Convert varchar into date PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related