How to transform date from P20210301 to DD.MM.YYYY in oracle-CodePudding

I have a date column P20210301. How to tranform this date to format 01.03.2021?

CodePudding user response：

This can be done by converting your text to a date datatype using TO_DATE, then converting it to the format you want as a string with TO_CHAR

SELECT TO_CHAR (TO_DATE ('P20210301', '"P"YYYYMMDD'), 'DD.MM.YYYY') AS new_date FROM DUAL;

CodePudding user response：

Database by default date stored in yyyy-mm-dd format so you have to manage a coding side

Note :if you use a PHP language then you use a date() function in PHP.

if any other language use like this:

TO_CHAR ( TO_DATE (str, 'YYYY/MM/DD'), 'DD.MM.YYYY')

Happy Coding...!!