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

8
votes

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.

3
dateplsqlformat
A DATE has no format, if just converts to a specific format when you ask the database or application to display it. If you need a specific formatted date, you need to convert from varchar to varchar instead. – Joachim Isaksson
I need output just in varchar or any datatype.. just it should be like '04/09/2013'in Select query. – Devashri B.
Try TO_DATE(SUBSTR(DOJ, 1, 11), 'dd/mm/rrrr') with oracle sql 11g – KingRider

3 Answers

12
votes

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.

4
votes

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
2
votes

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