Difference in minutes between two dates in Cognos Report Studio Report

[fshahul]

Hi,

I have two date fields Date1 and Date 2. I want to find the minutes difference between the two dates in Cognos report studio.

The format of the date is like Date1: Feb 17, 2014 9:36:36 AM, Date2: Feb 19, 2014 2:54:06 PM

Environment: Cognos 10.1.1 Report Studio
Database: Oracle
Package: Relational

Please help me on this. It is very urgent for me.

Thanks

[Lynn]

The result of subtracting two elements that are date or datetime data type is an interval. If you cast an interval to a VARCHAR it will be in a consistent format. For example, an interval that shows as

Code Select Expand

1 hour 7 minutes 27 seconds 737 milliseconds
will convert to a VARCHAR as

Code Select Expand

000000000 01:07:27.737000000

Note there is a leading space on the resulting string.

You can then substring out the elements and convert to minutes. Presumably you would ignore seconds and milliseconds and base the computation of minutes on the days, hours, and minutes components only.

Obviously you should test that these same assumptions are true in your environment. Assuming they are consistent, the below should work or get you close enough to figure out from here.

Cast the interval to a VARCHAR in a query item. I named mine "Interval String".

Code Select Expand


cast ( ( [DateTime1] - [DateTime2] ), VARCHAR(100) )



Parse out each element and multiply by the number of minutes in the component.

Code Select Expand


#/* Days */ #
( cast ( substring ( [Interval String], 1, 10 ), INTEGER ) * 1440 )
+
#/* Hours */#
( cast ( substring ( [Interval String], 12, 2 ), INTEGER ) * 60 )
+
#/* Minutes */#
( cast ( substring ( [Interval String], 15, 2 ), INTEGER ) )


Good luck!

[Joker]

The result of subtracting two elements that are date or datetime data type is an interval. If you cast an interval to a VARCHAR it will be in a consistent format. For example, an interval that shows as

Code Select Expand

1 hour 7 minutes 27 seconds 737 milliseconds
will convert to a VARCHAR as

Code Select Expand

000000000 01:07:27.737000000

Note there is a leading space on the resulting string.

You can then substring out the elements and convert to minutes. Presumably you would ignore seconds and milliseconds and base the computation of minutes on the days, hours, and minutes components only.

Obviously you should test that these same assumptions are true in your environment. Assuming they are consistent, the below should work or get you close enough to figure out from here.

Cast the interval to a VARCHAR in a query item. I named mine "Interval String".

Code Select Expand


cast ( ( [DateTime1] - [DateTime2] ), VARCHAR(100) )



Parse out each element and multiply by the number of minutes in the component.

Code Select Expand


#/* Days */ #
( cast ( substring ( [Interval String], 1, 10 ), INTEGER ) * 1440 )
+
#/* Hours */#
( cast ( substring ( [Interval String], 12, 2 ), INTEGER ) * 60 )
+
#/* Minutes */#
( cast ( substring ( [Interval String], 15, 2 ), INTEGER ) )


Good luck!

Hi Lynn,

Thanks for posting this.It helped me too.

I have one doubt::
If you cast an interval to a VARCHAR it will be in a consistent format-- How and why does this happen?

Thanks

[Lynn]

Hi Lynn,

Thanks for posting this.It helped me too.

I have one doubt::
If you cast an interval to a VARCHAR it will be in a consistent format-- How and why does this happen?

Thanks

I don't have a good answer for you, but if you were to cast an interval that spans days or years to varchar I suspect you'd see more elements populated that would need to be dealt with in order to get the right result.