CODEWITHSUNDEEP
postgresql
Jeff
Jeff

Reputation: 197

String to Date PostgreSQL

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

Upvotes: 0

Views: 143

Answers (2)

Klesley Gonçalves
Klesley Gonçalves

Reputation: 77

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

Upvotes: 0

Vivek S.
Vivek S.

Reputation: 21915

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

result

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

Upvotes: 1

Related Questions