The TO_EPOCH function expects a real Oracle Date so you will want to convert
your input date string
TO_EPOCH( TO_DATE ( {your date string}, 'yyyymmdd-HH24MISS'))
i.e. select TO_EPOCH( TO_DATE ( '20090818-134512', 'yyyymmdd-HH24MISS')) from
dual
Fred
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] On Behalf Of Kemes, Lisa
Sent: Tuesday, August 18, 2009 12:26 PM
To: arslist@ARSLIST.ORG
Subject: Re: Date Time Conversion Issue in Remedy
Thanks again Fred....
When the date/time gets updated in the table, does it have to be in this format
to convert over to Epoch?
Dd/mm/yyyy hh:mm:ss. It's going to come over as YYYYMMDD-HHMMSS. Should I
reformat it before converting it?
Lisa
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] On Behalf Of Grooms, Frederick W
Sent: Tuesday, August 18, 2009 1:12 PM
To: arslist@ARSLIST.ORG
Subject: Re: Date Time Conversion Issue in Remedy
I forgot to add ... If you want to know what time zones your Oracle has you can
query the V$TIMEZONE_NAMES view (Oracle 9i or higher)
select * from V$TIMEZONE_NAMES
Fred
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] On Behalf Of Grooms, Frederick W
Sent: Tuesday, August 18, 2009 12:09 PM
To: arslist@ARSLIST.ORG
Subject: Re: Date Time Conversion Issue in Remedy
Here are my Oracle functions to do the conversion. Change the 'US/Central' to
the time zone your database is using. The nice thing about using Oracle to do
the GMT offset conversion is it will automatically handle the US Daylight
Savings Time adjustments.
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;
/
Fred
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] On Behalf Of Kemes, Lisa
Sent: Tuesday, August 18, 2009 11:52 AM
To: arslist@ARSLIST.ORG
Subject: Re: Date Time Conversion Issue in Remedy
I'm having a similar issues (details are a little bit different), but how do
you convert a MM/DD/YYYY HH:MM:SS to epoch time. We are using Windows 2003,
Oracle 10, ARS 7.0.1....
I've seen lots of info about converting Epoch to a readable date format, but
not the other way around....
Lisa
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum
Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum
Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
