**
Turns out I was one day off on the minimum Informix date -- it
should be Dec. 31, 1899 (see http://www.inquiry.com/techtips/info_pro/10min/10min1000/10min1000-1.asp).
Also, the Informix DATETIME data type goes back to Jan. 1, 0001. So,
it should be possible to create a similar conversion to Informix DATETIME if
needing to convert a date from Remedy in the range between Jan. 1, 0001 and Dec.
30, 1899.
In any case, here is
the revised Informix procedure using the DATE data type,
which corrects for the missing day:
--INFORMIX:
CREATE PROCEDURE
"informix".remedydate_to_informix(i INTEGER) RETURNING DATE;
DEFINE d
DATE;
DEFINE n INTEGER;
IF i < 2415020 THEN
LET d =
MDY(12,31,1899);
ELSE
LET n = (i - 2415020);
LET d = MDY(12,31,1899) + n UNITS DAY;
END IF;
RETURN d;
END
PROCEDURE;
--Thomas
----- Original Message -----
Newsgroups:
gmane.comp.crm.arsystem.general
Sent: Thursday, September 28, 2006
18:08
Subject: Re: Integer date conversion in
Business Object WebIntelligence
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
>
__20060125_______________________This posting was submitted with HTML in it___
|
