Oh, what the heck... here are the methods for the three databases I
mentioned :-)
--MS-SQL:
CREATE FUNCTION dbo.remedydate_to_datetime(@dateint int)
RETURNS DATETIME AS
BEGIN
DECLARE @sql_date datetime;
IF @dateint < 2361331
SET @sql_date = '17530101'
ELSE
SET @sql_date = DATEADD(dd,@dateint - 2361331,'17530101');
RETURN @sql_date;
END
--INFORMIX:
CREATE PROCEDURE "informix".remedydate_to_informix(i INTEGER) RETURNING
DATE;
DEFINE d DATE;
DEFINE n INTEGER;
IF i < 2415021 THEN
LET d = MDY(1,1,1900);
ELSE
LET n = (i - 2415021);
LET d = MDY(1,1,1900) + n UNITS DAY;
END IF;
RETURN d;
END PROCEDURE;
--ORACLE (following is an example using the built-in function to convert the
date integer '2449992'):
SELECT TO_CHAR(TO_DATE(2449992, 'J'), 'MON-DD-YYYY') FROM DUAL;
Keep in mind, MS-SQL dates only go back to Jan 1, 1753 and Informix dates
only go back to Jan 1, 1900. Oracle dates cover the same range as Remedy's
Date fields -- back to Jan. 1, 4713 B.C.
HTH,
Thomas
----- Original Message -----
From: "Thomas Bean" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Thursday, September 28, 2006 16:49
Subject: Re: Integer date conversion in Business Object WebIntelligence
Hey Steve,
What RDBMS are you working with? I've created several user-defined
functions in MS-SQL and Informix that perform these conversions, also
Oracle has a built-in function that should work.
--Thomas
----- Original Message -----
From: "Steve McDonald" <[EMAIL PROTECTED]>
Newsgroups: gmane.comp.crm.arsystem.general
Sent: Thursday, September 28, 2006 15:47
Subject: Integer date conversion in Business Object WebIntelligence
Hello all,
Just wondering if anyone has tackled the database stored integer for
Remedy
date fields to a human readable date? If so, I'd love your help. I
figure
with BO and Remedy being partners there would be something built in but I
cant find it.
Steve
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
