How to convert number as date time in oracle

Question

I have a string as 08012016111512 where first two characters 08 is date, next two characters 01 is month, next four characters 2016 is year ,next two characters 11 is hour,next two characters 15 is minute and last two characters 12 is seconds. Suggest me how to convert above string as date time.

http://stackoverflow.com/questions/7920637/convert-a-string-date-into-datetime-in-oracle — tt_emrah, Jan 12 '16 at 04:47

score 4 · Accepted Answer · answered Jan 12 '16 at 05:19

You need to use TO_DATE function to explicitly convert the string literal into DATE.

to_date('08012016111512', 'DDMMYYYYHH24MISS')

For example,

SQL> alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

Session altered.

SQL> SELECT to_date('08012016111512', 'DDMMYYYYHH24MISS') my_date FROM dual;

MY_DATE
-------------------
08-01-2016 11:15:12

NOTE : The alter session statement is only to display the date in desired format.

How to convert number as date time in oracle

1 Answers1