How to change a date format in postgres?

Question

I am trying to convert a character varying date field to the format YYYYMMDD and below is the select query I tried. The output format is 1999-04-27 (YYYY-MM-DD). Admindate is the field name in the table.

select to_date( admindate,'MMDDYYYY')  from test;

Can someone please advice what I am doing wrong?

Is admindate of type varchar? If so, In what format is the date stored? — Gurwinder Singh
– Gurwinder Singh, Commented Feb 14, 2017 at 20:01
Oops, Sorry for the wrong question. I am trying the change the format to YYYYMMDD. — user2926497
– user2926497, Commented Feb 14, 2017 at 20:04
The real question is: why on earth are you storing a date in a varchar column? You should fix that as soon as possible — user330315
– user330315, Commented Feb 14, 2017 at 20:17

Gurwinder Singh · Accepted Answer · 2017-02-14 20:03:28Z

8

Use to_char to convert the converted date into the desired format.

select to_char(to_date(admindate, 'MMDDYYYY'), 'YYYYMMDD')
from test;

answered Feb 14, 2017 at 20:03

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

Comments

Jim Jones · Accepted Answer · 2021-02-17 16:54:56Z

5

you can try this

SELECT TO_CHAR(NOW() :: DATE, 'dd/mm/yyyy');

edited Feb 17, 2021 at 16:54

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

answered Feb 17, 2021 at 16:13

Anzo

511 silver badge1 bronze badge

1 Comment

christopher.online Over a year ago

Please add a short explanation to your answer

Collectives™ on Stack Overflow

How to change a date format in postgres?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related