These are the 2 Oracle 10g functions I use. In your case change
'US/Central' to 'US/Pacific'. Run these in SQL Plus as your ARAdmin
user
CREATE OR REPLACE FUNCTION FROM_EPOCH (secs IN NUMBER)
RETURN DATE
IS
lDate DATE;
BEGIN
lDate := (From_TZ((TO_DATE('01/01/1970',
'dd/mm/yyyy')+(secs/86400)),'GMT') AT TIME ZONE 'US/Central');
RETURN lDate;
END From_Epoch;
CREATE OR REPLACE FUNCTION TO_EPOCH (lDate IN DATE)
RETURN NUMBER
IS
Seconds NUMBER;
dte1 TIMESTAMP;
dte2 TIMESTAMP;
BEGIN
dte1 := new_time(lDate, to_char( from_tz(cast (lDate as timestamp),
'US/Central'),'TZD'),'GMT');
dte2 := TO_TIMESTAMP_TZ('01/01/1970 '|| TZ_Offset('Greenwich'),
'MM/DD/YYYY TZH:TZM');
Seconds := (((dte1-0) - (dte2-0)) * 86400);
RETURN Seconds;
END To_Epoch;
Examples of use:
Select C1, From_Epoch(C3) as CreateDate, C101 from T1;
Select * from User_X Where Create_Date < To_Epoch('01 JAN 2002');
Select * from User_X Where Create_Date < To_Epoch(TO_DATE('01/01/2002
00:00:00','MM/DD/YYYY HH24:MI:SS'));
Fred
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of surya4u
Sent: Monday, February 02, 2009 1:53 PM
To: [email protected]
Subject: Converting remedy timestamp into oracle date/time format
I need a formula that converts the remedy timestamp into oracle
date/time
field...
I have referred to KB article:000000006132 and formula given in the
article
is not working...
SELECT TO_CHAR(TO_DATE('01/01/1970 00:00:00', 'MM/DD/YYYY HH24:MI:SS') +
(C3 / ( 60 * 60 * 24 )),
'MM/DD/YY HH24:MI:SS')
FROM T40;
I am in PST timezone and need formula that converts into oracle
date/time
format....
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are"
