Remedy stores all Date/Time values in the database as numbers. Since you are
using Oracle you can add the following functions to your Oracle db and
reference them in your view (not both functions will automatically handle the
Daylight savings time change if your timezone needs it).
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 'US/Central');
RETURN lDate;
END From_Epoch;
/
CREATE OR REPLACE FUNCTION TO_EPOCH (lDate IN DATE)
RETURN NUMBER
IS
Seconds NUMBER;
dte1 TIMESTAMP;
dte2 TIMESTAMP;
BEGIN
dte1 := new_time(lDate, to_char( from_tz(cast (lDate as timestamp),
'US/Central'),'TZD'),'GMT');
dte2 := TO_TIMESTAMP_TZ('01/01/1970 '|| TZ_Offset('Greenwich'), 'MM/DD/YYYY
TZH:TZM');
Seconds := (((dte1-0) - (dte2-0)) * 86400);
RETURN Seconds;
END To_Epoch;
/
Change the 'US/Central' value to the correct Oracle timezone that your Remedy
server is using. In your view use to TO_EPOCH function to convert the Oracle
date to a Remedy number value. In the form definition create a Date/Time field
and manually put the DATE_HIRED value in the database field value and Remedy
will display the date correctly.
Fred
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] On Behalf Of RKVKRKVK
Sent: Monday, November 09, 2009 12:02 AM
To: arslist@ARSLIST.ORG
Subject: Create Date components using VIEW Forms
Hi Listers,
Please look into the following issue.
I created the following table in oracle (Manually).
CREATE TABLE MIGRATE_DATE ('EMP_LAN_ID' char(10), 'FIRST_NAME' char(10),
DATE_HIRED DATE);
Then i created view for the above table using the following SQL Syntax.
CREATE OR REPLACE FORCE VIEW MIGREATE_DATE_VIEW ("ROW_NUM", "EMP_LAN_ID",
"FIRST_NAME", "DATE_HIRED") as
SELECT ROW_NUM, FIRST_NAME, EMP_LAN_ID, DATE_HIRED
FROM
MIGREATE_DATE;
It created the view in the oracle db,
Now i am trying to create VIEW form using Developer Studio for the above
mentioned view, But when i create, it is showing only character and integer
fields (Except DATE Fields in the view form) in the VIEW form selection
(Developer Studio).
But if i try to put DATE Fields as character, then it is showing DATE_HIRED
as character field in the VIEW form (Developer Studio).
Please suggest me to achieve the above problem to create date fields in the
VIEW form using Developer Studio.
Best Regards,
Krishna B
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
