I find the best way to do something like this is to use a database function
CREATE OR REPLACE FUNCTION FROM_EPOCH_TZSTR (secs IN NUMBER,
strFormat IN VARCHAR2 := 'MM/DD/YYYY
HH12:MI:SS AM TZD',
strTZ IN VARCHAR2 := 'US/Central' )
RETURN VARCHAR2
IS
lDate TIMESTAMP WITH TIME ZONE;
newTZ VARCHAR2(64);
BEGIN
Select NVL((Select Min(tzName) from gv$timezone_names Where tzName =
strTZ),'US/Central') into newTZ from Dual;
lDate := (From_TZ((TO_DATE('01/01/1970', 'dd/mm/yyyy')+(secs/86400)),'GMT')
AT TIME ZONE newTZ);
RETURN TO_CHAR(lDate, strFormat);
END From_Epoch_TZstr;
/
In Remedy you would do a filter with actions of
Action 1: Set Fields: Setting a Display Only integer field (zTempInteger) =
Remedy Date Time field
Action 2: SQL Set Fields: Set a character field = SELECT
FROM_EPOCH_TZSTR($zTempInteger$, 'MM/DD/YYYY HH24:MI:SS','US/CENTRAL') FROM DUAL
Fred
From: Action Request System discussion list(ARSList)
[mailto:[email protected]] On Behalf Of jaya munjal
Sent: Wednesday, June 29, 2011 2:43 PM
To: [email protected]
Subject: Convert Datetime field to GMT
**
Hi List
I am working on an integration of remedy with Siebel and have a requirement to
convert the date time field to GMT time zone and 24 hrs format while the remedy
application server is in CST timezone and using standard time format (AM/PM).
any ideas whats the best way to implement this?
Thanks
Jaya
Environment Details
ARS 7.6.03
OS - Windows 2008
DB - Oracle 10g
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
