sql - How to convert date stored as VARCHAR2 to 'MM/DD/YYYY HH24:MI:SS'?

Question

I am looking to convert a date (stored as varchar2) e.g: 4/19/2016 6:42 to this date format MM/DD/YYYY HH24:MI:SS.

Source field data type - varchar2

target field data type - date

source value - 4/19/2016 6:42

expected value - 04/19/2016 06:42:00

Can someone help with the Oracle (SQL) command for this? SQL code would be preferred over PL/SQL, but anything works.

Answer 1

You should convert in date first and then in char

 SELECT TO_CHAR(TO_DATE(your_var_char_date,'MM/DD/YYYY  HH24:MI:SS'), 'MM/DD/YYYY  HH24:MI:SS') 
 FROM  your_table;

obviuosly if is necessary only the conversion to date should be used

SELECT TO_DATE(your_var_char_date,'MM/DD/YYYY  HH24:MI:SS')
FROM  your_table;