How to update the date format in postgres?

Question

I have dates in the database with the format YYYYMMDD. But i would like to update them all to MM/DD/YYYYY.

How do i do that?

Clodoaldo Neto · Accepted Answer · 2014-04-02 14:40:00Z

1

There is no format stored in the date/time data types. If you want an specific output format use the to_char function

select to_char(current_date, 'MM/DD/YYYY');

If the date column is text then first cast it to date to use the above function

select to_char(start_date::date, 'MM/DD/YYYY');

edited Apr 2, 2014 at 14:40

answered Apr 2, 2014 at 13:55

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

5 Comments

user2926497 Over a year ago

Thanks. But the interface doesnt support/show the current format of the date (YYYYMMDD) in the database. So i will have to update the date in the database to mm/dd/yyyy. I tried to update using " Update <TableName> set start_date = to_char(current_date,'MM/DD/YYYY'); " .But it gives me the error " to_char(start_date, 'M... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts." Can you please assist?

Clodoaldo Neto Over a year ago

@user What is the interface? Are you sure you are using Postgresql? Do select version()

Clodoaldo Neto Over a year ago

@user Is the start_date text? Post the output of \d my_table

user2926497 Over a year ago

Yes. Start_Date is a varchar.It is postgres 8.4

user2926497 Over a year ago

BELOW WORKED. THANKS A LOT. select to_char(start_date::date, 'MM/DD/YYYY')

Collectives™ on Stack Overflow

How to update the date format in postgres?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related