Convert date which is a text data type to specific format in PostgreSQL

Question

How can we convert 15jan2018 which is a text datatype to 15JAN2018.

I am able to convert if it is date datatype.

Are you actually converting anything? Seems like you just need UPPER case. — Jacob H
– Jacob H, Commented Feb 22, 2019 at 18:33

Laurenz Albe · Accepted Answer · 2019-02-22 18:29:25Z

1

You can use to_date:

SELECT to_date('15jan2018', 'DDmonYYYY');

  to_date   
------------
 2018-01-15
(1 row)

Then use to_char to convert it to any format you want:

SELECT to_char(to_date('15jan2018', 'DDmonYYYY'), 'DDMONYYYY');

  to_char  
-----------
 15JAN2018
(1 row)

Of course you can simply use upper in your specific case to convert it to upper case, but I guess that's not what you are looking for.

Finally, PostgreSQL's date input function is pretty flexible, so you can try to simply cast it:

SELECT '15jan2018'::date;

    date    
------------
 2018-01-15
(1 row)

This is useful if the format varies slightly.

answered Feb 22, 2019 at 18:29

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

1 Answer 1