Incorrect Output when converting TEXT to Date ('yyyy-mm-dd') format (Postgresql)-CodePudding

After I use the following code to convert column 'birthdate' (table: animals) from CHAR to Date, dates with incorrect year are getting displayed.

This is how 'birthdate' looks like in char format:

01/18/2019
02/05/2005
05/06/2013 ...

Code:

ALTER TABLE animals ALTER COLUMN birthdate TYPE DATE 
using to_date(birthdate, 'YYYY-MM-DD');

Output I am getting is something like this:

0019-01-18
0005-02-05
0013-05-06 ....

It should be:

2019-01-18
2005-02-05
2013-05-06 ...

Seems like '2' is replaced by '0' in the yyyy part of the date. This is incorrect, any idea why is this happening? The format I want is 'yyyy-mm-dd'.

Thank you for your help.

CodePudding user response：

I assume that the initial data type of birthday is text. In this case your format string YYYY-MM-DD does not match the format of data. It should be MM/DD/YYYY.

ALTER TABLE animals ALTER COLUMN birthdate TYPE DATE 
using to_date(birthdate, 'MM/DD/YYYY');

Please note that once the type of birthdate is changed to date (or it is date and no alter table is needed at all) it has no format anymore. You can format or rather present it as you wish using to_char.

select to_char(birthdate, 'YYYY-MM-DD');

CodePudding user response：

You need to typecast the column type to date. try below query.

ALTER TABLE animals ALTER birthdate type date
using      (birthdate::date)