Date/Time format (Impromptu Report)

[Drepso]

I have created a calculation in an impromptu report to return an interval between two date/time timestamps.

The impromptu report is retrieving records via an oracle database and the format of the field generated is:

0 00:00:50:000

I have been asked to calculate an average of this field to be used as a measure in a Cognos Cube.

I have tried changing the formatting properties in the field datasource as well as in the calculated measure but I always receive "data input conversion or overflow errors" as an error message.

Can anyone assist me with the above?

Thanks in advance

 

[griffindm]

Drepso,

Try using a Round function to limit the scalar properties of the returned value.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[Drepso]

Thanks Dave

I looked at the round functions but cannot select the calculated field I generated as it is not in the report fields picklist. I assume this is because it is looking for numeric not date/time format.

The interesting thing is if I run a report to display the two date/time fields used in the calculation; the format displayed is: dd/mm/yyyy hh:mm:ss

however if i subtract these two fields it is:

0 00:00:50:000

Also are you referring to the round near function?

Appreciate your efforts so far.

Thanks

 

[sandeep0881]

Hi,
U can use the function ymdint_between(date1,date2) to get the range between two dates date2 > date1.The output returned is in the format of mm/dd/yyyy hh:mm AM/PM.
It's mentioned that this conversion cannot be used in other mathematical calculations..However after converting it to string i think u can do some manipulations to get the average.

hope this helps..

Sandeep.