If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Datetime difference in days w/decimal

Started by JCarter, 03 Apr 2023 08:12:32 AM

Previous topic - Next topic

JCarter

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