[FM] subtract 2 dates oracle style to come up with day-fraction

[blom0344]

Basically, when you subtract 2 dates in Oracle the result returned is the fraction in days (provided time portion is filled)

I need this time difference to based calculations on.

Framework only accepts this subtraction when local processing is activated, but then it returns a time interval which is the duration formatted to a nice format useless to perform calcs on.

Anyone an idea how  to solve this?

[blom0344]

Cognos KB has a reference 1029733.1  on this topic.
From within Framework manager setting the format of TIME INTERVAL to a specific format (such as minutes) does not change the way the outcome of the subtraction is presented. But I hope it is correctly presented within the studios...

[blom0344]

Works as designed   ;)

[MFGF]

In Impromptu, I remember having to use the DAY function on the interval to extract the day part as an integer before using in further calculations.  Probably worth a try here too, although you'd probably need to use the extract function rather than the day function

eg: extract(day, ([date2] - [date1])

Regards,

MF.

[blom0344]

Thank you , that seems to work quite nicely.

To sum up what we tested (for Oracle):

Calculation 1:  "Whole days without having to switch on local processing"

to_char([namespace].[namespace1].[Date1];'J') - to_char([namespace].[namespace1].[Date2];'J')

Calculation 2:  "Whole days and being forced to switch on local processing":

extract(day,[namespace].[namespace1].[Date1]-[namespace].[namespace1].[Date2])

Calculation 3:  "Timeinterval to be formatted and being forced to switch on local processing":

[namespace].[namespace1].[Date1]- [namespace].[namespace1].[Date2]

Calculations 2 and 3 seem to have the same use.
However, the datatype of calculation2  is Int32,  and that of calculation3 is Time Interval