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
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the
Answers Are"
