Mark,
If you are on Oracle 9i (or higher) try this (Let Oracle's time zone
functions do the conversion for you. Just change the 'America/Chicago'
to your zone).
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 'America/Chicago');
RETURN lDate;
END From_Epoch;
/
Steve
I believe Sybase has the DateAdd function so you can do
dateadd(date_part, integer, date expression) to get it to a date. You
will probably still have to convert for the time zone.
i.e.
dateadd( ss, Remedy_Epoch_Value, "01/01/1970")
Fred
________________________________
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Mark Rushton
Sent: Wednesday, October 17, 2007 5:32 AM
To: [email protected]
Subject: Re: T-SQL(Sybase) code to convert EPOCH (remedy) integer to
date format and vice versa
**
Steve
Don't have anything for Sybase but this is what we have as an Oracle
function:-
CREATE OR REPLACE FUNCTION Ds ( in_date NUMBER ) RETURN DATE IS
wrk_number NUMBER;
wrk_date DATE;
wrk_month VARCHAR2(3);
wrk_year VARCHAR2(4);
march_sunday DATE;
oct_sunday DATE;
BEGIN
IF in_date IS NULL THEN
RETURN NULL;
END IF;
wrk_date := TO_DATE('01-JAN-1970 00:00:00', 'DD-MON-RRRR
HH24:MI:SS');
wrk_date := wrk_date + (in_date/86400);
wrk_month := TO_CHAR(wrk_date, 'MON');
wrk_year := TO_CHAR(wrk_date, 'RRRR');
wrk_number := 31;
march_sunday := NULL;
WHILE march_sunday IS NULL AND wrk_number > 0 LOOP
IF TO_CHAR(TO_DATE(TO_CHAR(wrk_number)||'-MAR-'||wrk_year,
'DD-MON-RRRR'),
'fmDY') = 'SUN'
THEN
march_sunday :=
TO_DATE(TO_CHAR(wrk_number)||'-MAR-'||wrk_year||' 020000',
'DD-MON-RRRR HH24MISS');
ELSE
wrk_number := wrk_number - 1;
END IF;
END LOOP;
wrk_number := 31;
oct_sunday := NULL;
WHILE oct_sunday IS NULL AND wrk_number > 0 LOOP
IF TO_CHAR(TO_DATE(TO_CHAR(wrk_number)||'-OCT-'||wrk_year,
'DD-MON-RRRR'),
'fmDY') = 'SUN'
THEN
oct_sunday :=
TO_DATE(TO_CHAR(wrk_number)||'-OCT-'||wrk_year||' 020000',
'DD-MON-RRRR HH24MISS');
ELSE
wrk_number := wrk_number - 1;
END IF;
END LOOP;
IF wrk_date BETWEEN march_sunday AND oct_sunday THEN
wrk_date := wrk_date + (1/24);
END IF;
RETURN wrk_date;
END;
/
Mark Rushton
IBM Global Services
Remedy Engineering & Development Team
Email: [EMAIL PROTECTED]
Tel: 01962 822863
Mob: 07930 336843
Steve <[EMAIL PROTECTED]>
Sent by: "Action Request System discussion list(ARSList)"
<[email protected]>
17/10/2007 11:08
Please respond to
[email protected]
To
[email protected]
cc
Subject
T-SQL(Sybase) code to convert EPOCH (remedy) integer to date format and
vice versa
**
Hi All,
I tried searching the archive before I posted a mail, but did not get
any relevant threads.
Is there any T-SQL (Sybase) code to
1) Convert from remedy/epoch time (ex:1137126330) to actual date/time
ex: (13/01/2006 09:55:30) and
2) Vice versa?
Thank you.
__20060125_______________________This posting was submitted with HTML in
it___
________________________________
Unless stated otherwise above:
IBM United Kingdom Limited - Registered in England and Wales with number
741598.
Registered office: PO Box 41, North Harbour, Portsmouth, Hampshire PO6
3AU
__20060125_______________________This posting was submitted with HTML in
it___
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
