Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
277 views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...