CREATE OR REPLACE FUNCTION problem
Date: Wed, 30 Jun 2010 03:59:39 -0700 (PDT)
Message-ID: <f2a4629c-0b3a-455c-98a4-2ad1f5aa4c68_at_j4g2000yqh.googlegroups.com>
Hello,
I need a function that converts date_to_unixtime. I found a good select example in the post #4 by Mark Schrijver: http://jehiah.cz/archive/oracle-date-to-unix-timestamp
I modified it a little and tried to make a function out of this select. There is an error in the function, I can not figure it out. Please help.
<pre>
CREATE OR REPLACE FUNCTION CURRENT_UNIXTIME_UTC_SEC RETURN number IS
xt number;
BEGIN
SELECT d * (24*60*60) + h * (60*60) + (m * 60) + s as unix_time
FROM (
SELECT to_number(rtrim(substr(dt, 2, instr(dt, ' ')-1))) d,
to_number(substr(dt, instr(dt, ' ')+1, 2)) h, to_number(substr(dt, instr(dt, ':')+1, 2)) m, to_number(substr(dt, instr(dt, '.')-2, 2)) s,dt
FROM ( SELECT to_char(dt) dt
FROM (
SELECT (
sys_extract_utc(current_timestamp) - TO_TIMESTAMP('01.01.1970','DD.MM.YYYY')) dt FROM dual
))) INTO xt;
RETURN xt;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20777,'An error encountered - '||
SQLCODE||' ERROR: '||SQLERRM);
END;
</pre>
Thank you! Received on Wed Jun 30 2010 - 05:59:39 CDT