Hi I have stored no. of seconds from 1970 in number datatype on a table and I need to access this number and display it in Date format mm/dd/yyyy using PL/SQL.
Can anyone help?
Printable View
Hi I have stored no. of seconds from 1970 in number datatype on a table and I need to access this number and display it in Date format mm/dd/yyyy using PL/SQL.
Can anyone help?
VERY easy to do, IF you know date arithmetic in Oracle :
--
-- Convert Oracle date to number of seconds since reference date MDP
--
select to_number(sysdate - to_date('01-JAN-1970','DD-MON-YYYY'))
* (24 * 60 * 60) "C Time format"
from sys.dual
;
--
-- Convert from number of seconds since reference date to Oracle date
--
select to_char(to_date('01-JAN-1970','DD-MON-YYYY') +
( &no_sec_since_70 / (60 * 60 * 24) ),'DD-MON-YYYY HH24:MI:SS')
"New Date"
from sys.dual
;
Regards,
Chiappa