Solved

need help with datetime calculations

Posted on 2011-03-03
16
940 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:tagomtech
  • 6
  • 6
  • 3
  • +1
16 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 35028501
One would hope '1.48' minutes is 1 minute 48 seconds, not some sort of decimal ...?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35028523
0
 

Author Comment

by:tagomtech
ID: 35028554
yes it is
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35028585
The above class will do  exactly what you need: use the minutes and seconds fields of the array
0
 

Author Comment

by:tagomtech
ID: 35028596
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35028623
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
0
 

Author Comment

by:tagomtech
ID: 35028672
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35028742
You can do the calculation in seconds then convert for display.  That is how it is done in Crystal.

mlmcc
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:tagomtech
ID: 35028877
how would i do that, i have tried ten ways to egypt!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35029077
I don't know either Java or iReports so I don't know.

mlmcc
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35029738
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

0
 
LVL 86

Accepted Solution

by:
CEHJ earned 500 total points
ID: 35029751
Or for your 'decimal' display:


String minAndSec = String.format("%d.%d", minutes, seconds); //mm.ss

Open in new window

0
 
LVL 92

Expert Comment

by:objects
ID: 35031194
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?
0
 

Author Comment

by:tagomtech
ID: 35057607
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

0
 

Author Closing Comment

by:tagomtech
ID: 35057770
still trying to format but this is the correct method - ireports is just handling differently
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 35059748
:)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now