Calculate datetimediff in hrs mins seconds using function alias in reports(Oracle Type)

General
, ,
1

Sol:

Create custom function Alias

Step 1: Just copy by saving as the OOTB DifferenceinMinutes function alias.

Step 2: replace with below

<p:choose>
<p:when test=“.pyParameters(1).pyUserEnteredDataType == ‘DATETIME’ && .pyParameters(2).pyUserEnteredDataType ==‘DATETIME’”>
<p:choose>

<p:when test=".pyParameters(1).pyDBDataType ==‘TIMESTAMP’ && .pyParameters(2).pyDBDataType ==‘TIMESTAMP’ ">

ROUND(( CAST({2} AS DATE) - CAST({1} AS DATE) )*86400)
</p:when>

</p:choose>
</p:when>

<p:otherwise>
ERROR : INCOMPATIBLE TYPES ENTERED
</p:otherwise>

</p:choose>

  1. Apply in Report definition

  2. In RD row apply custom HTML property (Control)to the property that needs the date in this format days,hrs,mins,secs. ( aka Control that displays as 2days, 3 hrs, 2mins, 6 secs format). For this create below custom control

  3. Saves OOTB “DateDifference” control to your custom name say for ex “DateDifferenceInSeconds”

  4. Then eplace with below code :

<%
double totalSecs=0;
totalSecs = Double.parseDouble(tools.getActive().getStringValue());

String display;

if(totalSecs>0){

double dDays = Math.floor(totalSecs / 3600); // get hours
double dHours = Math.floor(totalSecs / 3600); // get hours
double dMins = Math.floor((totalSecs - (dHours * 3600)) / 60); // get minutes
double dSecs = totalSecs - (dHours * 3600) - (dMins * 60); // get seconds

display = Integer.toString((int)dDays) + " Days, " + Integer.toString((int)dHours) + " Hours, " + Integer.toString((int)dMins) + "Mins, " +Integer.toString((int)dSecs) + “Secs” ;

} else {

display ="0 Days, " + " 0 Hours, " + "0 Mins, "+“0 Secs”;
}

%>

<%=display%>