Thursday 24 January 2013

How to convert unix number to date data type in oracle or mysql

In Oracle:
1)      At first create a function as like as “unixnumber_to_oracledate
CREATE OR REPLACE FUNCTION unixnumber_to_oracledate(p_unixdatetimenumber IN number)
RETURN date IS
V_date date:=systimestamp;
BEGIN
   select TO_DATE('19700101000000','YYYYMMDDHH24MISS') + NUMTODSINTERVAL(p_unixdatetimenumber, 'SECOND')
   INTO V_date
   FROM dual;  
   RETURN V_date;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END unixnumber_to_oracledate;
2)       How to Call this function:
        SQL>Select unixnumber_to_oracledate(1244108886) from dual;
In MySQL:
   There is a Built-in-function FROM_UNIXTIME()
   Example:  
Mysql>SELECT FROM_UNIXTIME(1351907992);