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:arsl...@arslist.org] On Behalf Of surya4u Sent: Monday, February 02, 2009 1:53 PM To: arslist@ARSLIST.ORG 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"