I'm using Cog 11.1.7 FP6 with an Oracle data source.
I feel like this is a simple thing, but maybe not? I have two datetime fields that I want to subtract one from the other and display the results in the number of days with decimal portion that covers hours/minutes/seconds/etc.
I've tried doing this with Cognos data format, and finally found a solution using extract and cast, but it just seems more complex than it needs to be.
Am I missing some simple functionality?
Query:
Col 1: SOURCE.TABLE.DATEFIELD1
Col 2: SOURCE.TABLE.DATEFIELD2
Col 3: SOURCE.TABLE.DATEFIELD1 - SOURCE.TABLE.DATEFIELD2
Col 4 (my solution): extract(minute,cast(SOURCE.TABLE.DATEFIELD1-SOURCE.TABLE.DATEFIELD2,interval minute)) / 1440
Dropped the query into a simple list with no data formatting set and got result:
Col 1: May 6, 2010 12:07:42 PM
Col 2: Jul 31, 2003 9:05:15 AM
Col 3: 2471 days 3 hours 2 minutes 27 seconds
Col 4: 2471.12638889
EDIT: Reformatted to remove square brackets