String to Date PostgreSQL

Question

I have table name bill_date and this is my table row, the field type of create_date is character varying

id | create_date 
1  | 20080108
2  | 20080116

I want to convert create_date to Date but when I used this QUERY

SELECT to_date(create_date, 'YYYYddmm'),* FROM bill_date

I got wrong result

2008-08-01
2009-04-03

there something wrong in field type? any help. thanks

I suppose you would like the output to be 2008-01-08? Can I suggest you leave it in the format given to you by to_date, as this is ISO format? — Alex
– Alex, Commented Apr 16, 2015 at 5:29
I suppose you would like the output to be 2008-01-08? yes, and row 2 also wrong output. — Jeff
– Jeff, Commented Apr 16, 2015 at 5:30
The output of 2008-08-01 for the format YYYYddmm absolutely correct for the input value 20080108. 2008 = YYYY, 01 = dd, 08 = mm. If you want the input value to be parsed differently you need to use a different format mask, e.g. yyyymmdd — user330315
– user330315, Commented Apr 18, 2015 at 11:42

Vivek S. · Accepted Answer · 2015-04-18 10:03:51Z

1

SELECT '20080108'::DATE
    ,to_date('20080108', 'YYYYddmm')

result

date         to_date    
----------   ---------- 
2008-01-08   2008-08-01

answered Apr 16, 2015 at 5:32

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

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

Comments

Klesley Gonçalves · Accepted Answer · 2021-03-07 19:50:06Z

0

ALTER TABLE <tablename> ALTER COLUMN <columnname> TYPE DATE using to_date(<columnname>,'YYYYDDMM');

answered Mar 7, 2021 at 19:50

773 bronze badges