asked on

need help with datetime calculations

Jasper Reports uses java to calculate dates, crystal reports looks like you can create an sql query, I have created the same query in the st_proc.
ISSUE: coming up with three different results

code used in st_proc: datediff(S,clstartcalltime,a.cldatetime)/60 as [Response Time], rounds up and does not give the after decimal portion (1.48 minutes) - however this piece of code will need to be removed from the st_proc when I launch the reports. Currently I am merely trying to figure out why I am getting different results.

code used in crystal reports: (DateDiff ("s", {CC_Report_CrewHistory;1.Call Started}, {CC_Report_CrewHistory;1.Crew Filled}))/60 (This is the result that I must match using iReports - preferably without changing the st_proc.

code used in iReport (java) -$F{Crew Filled}.getMinutes()-$F{Call Started}.getMinutes() does not give me a double it returns an int - which rounds - I need to return or somehow cast this to a double - both crew filled and call started are timedate types in the database.

I am including two pics so that you can see the results of each piece of code. Any help is GREATLY appreciated.
crystalreports.JPG
ireport-java-and-sql-results.JPG

CEHJ

One would hope '1.48' minutes is 1 minute 48 seconds, not some sort of decimal ...?

CEHJ

See http://technojeeves.com/joomla/index.php/free/71-difference-between-two-dates-in-java

tagomtech

ASKER

yes it is

CEHJ

The above class will do exactly what you need: use the minutes and seconds fields of the array

tagomtech

ASKER

This issue is in iReports or jasperServer it seems I am limited in how I can calculate the differences. The gettimedifference is not in jasperServer as a default.

Mike McCracken

ONe issue. In Crystal you are calculating the difference in seconds whereas in the Java you are only using the minutes. You somehow have to get the Java to return the values in seconds rather than minutes.

mlmcc

tagomtech

ASKER

I can do that with the getSeconds method...however it still only returns an int I need it to return a double so that I can show the seconds

Mike McCracken

You can do the calculation in seconds then convert for display. That is how it is done in Crystal.

mlmcc

tagomtech

ASKER

how would i do that, i have tried ten ways to egypt!

Mike McCracken

I don't know either Java or iReports so I don't know.

mlmcc

CEHJ

A double doesn't make any sense. For minutes and seconds, you can do

int seconds = x.getSeconds();
int minutes = seconds / 60;
seconds %= 60;
String minAndSec = String.format("%d:%d", minutes, seconds); //mm:ss

Open in new window

ASKER CERTIFIED SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mick Barry

with java it is easy to calculate date differences, but you need to do the calculations in your database query. What database are you using?

tagomtech

ASKER

I can not change the query, what i can do and how i think i am going to need to solve this is use the java.sql.timestamp method to calculate the differences.
however this method is a little confusing for me....This is what i have
callTime is being passed as timestamp
fillTime is being passed as timestamp

I need to use these to fields to figure out (java.sql.timestamp) the number of seconds (shown as a double) that has passed from start to endtime

tagomtech

ASKER

still trying to format but this is the correct method - ireports is just handling differently

CEHJ