Thank you very much Thomas. Best regards, Daniel
-----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Thomas Bean Sent: Friday, September 29, 2006 2:08 AM To: [email protected] 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 > ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org -- *****DISCLAIMER***** The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly prohibited and may be unlawful. Orange Romania S.A. is neither liable for the proper, complete transmission of the information contained in this communication nor any delay in its receipt. *****END OF DISCLAIMER***** _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
