This will help you.
You can try this:
to_char(to_date(remedydateonlyfield, 'j'), 'mm/dd/yyyy') as remedydateonlyformated from dual;
As Rehman Sir replied here.
You may also try - as you said you were able to fetch date time from a normal remedt integer.
You can pass above datetime to To_date function to get this im required format.
TO_DATE( string1, [ format_mask ], [ nls_language ] )
Parameters or Arguments
string1 is the string that will be converted to a date.
format_mask is optional. This is the format that will be used to convert string1 to a date.
nls_language is optional. This is the nls language used to convert string1 to a date.
For a Date/Time field in BMC ARS I use the following Oracle function to convert the Remedy integer value to an Oracle date value (Of course use the correct time zone that your system is running under):
CREATE FUNCTION FROM_EPOCH (secs IN NUMBER) RETURN DATE
lDate DATE := NULL;
IF SECS IS NOT NULL THEN
lDate := (From_TZ((TO_DATE('01/01/1970', 'dd/mm/yyyy')+(secs/86400)),'GMT') AT TIME ZONE 'America/Chicago');
In your SQL you would say something like
SELECT FROM_EPOCH(CREATE_DATE) FROM USER_X;
For Date Only fields BMC uses the Oracle Julian so you can say
SELECT TO_DATE( date_only_field, 'J' ) FROM view;
Fred Grooms I have not used FROM_TZ oracle function and to be honest not aware of it.
I have a question related to this.
Will this function take care of all previous Day light saving dates which were different start and end date as of today?
There are no date restrictions to the function so all dates will work.
The FROM_TZ function was added in Oracle 9 (I think)
Edit: I checked and it was added in Oracle 9i (so anyone on ARS 6.3 and later should be good)
Thanks for your help, we got reply from user, they used the below query.
(select to_date(date_field,'J') from dual)
Result comes as "31-DEC-13" - we want format to be "12/31/2013" and do not know how to convert. I will consult our database team if they can help.
select to_char(to_date(date_field,'J'),'mm/dd/yyyy') from dual.