Convert varchar2 to Date ('MM/DD/YYYY') in PL/SQL

Question

I need to convert string from varchar to Date in 'MM/DD/YYYY' format. My input string is '4/9/2013' and my expected output is '04/09/2013'. i.e. 2 digit month, 2 digit date and 4 digit year seperated by'/'

I have below data: DOJ varchar2(10 Byte)column has '4/9/2013' value. I am using 'select TO_DATE(DOJ,'MM/DD/YYYY') from EmpTable. But I am getting result as 09-APR-13. I want it in 04/09/2013 format.

Please advise. Thanks in advance.

Answer 1

Easiest way is probably to convert from a VARCHAR to a DATE; then format it back to a VARCHAR again in the format you want;

SELECT TO_CHAR(TO_DATE(DOJ,'MM/DD/YYYY'), 'MM/DD/YYYY') FROM EmpTable;

An SQLfiddle to test with.

Answer 2

First you convert VARCHAR to DATE and then back to CHAR. I do this almost every day and never found any better way.

select TO_CHAR(TO_DATE(DOJ,'MM/DD/YYYY'), 'MM/DD/YYYY') from EmpTable

Answer 3

Example query: SELECT TO_CHAR(TO_DATE('2017-08-23','YYYY-MM-DD'), 'MM/DD/YYYY') FROM dual;