Solved

Crystal Reports datediff

Posted on 2008-10-17
8
754 Views
Last Modified: 2010-04-21
Hi

I am using a SQL database, I am trying to create a report and one of the items I want is for the report to show the time differences between to dates. I am trying to create the formula. I am using the DateDiff, I add in the required fields and when i do the check it tells me : A date is required here.

any ideas?
0
Comment
Question by:riley00
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 22742119
Are the fields dates, datetimes, or strings?

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22742123
Can you show the formula?

mlmcc
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 22742391
the dates are probably strings.

Make sure they are dates. If they are not wrap them in a date() function. Then do a datediff('s',date1,date2).  Then convert the second to a time using this formula

WhilePrintingRecords;
NumberVar TotalSec :=  {YourTable.TotalSeconds};
NumberVar Days    := Truncate  (TotalSec / 86400);
NumberVar Hours   := Truncate  (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate  (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;
Totext ( Days ,    '##' ) +  ':' +
Totext ( Hours ,   '00' ) +  ':' +
Totext ( Minutes , '00' ) +  ':' +
Totext ( Seconds , '00' )

**Retrieved from website and credit to Ken Hamady http://www.kenhamady.com/
0
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 

Author Comment

by:riley00
ID: 22743265
I got the datediff to work, and it returns the seconds, but how do I put those in to NumberVar TotalSec :=  {YourTable.TotalSeconds};

will that have to be a different formula?
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 22743288
No, you put the formula name into the {YourTable.TotalSeconds}.

So if you named your formula seconds, then in the Field tree find the formula and double click on it. The rest should work from there.
0
 

Author Comment

by:riley00
ID: 22743339
I must be missing this? I dont have a totalseconds, I have dates that I was doing a datediff that returned seconds. And it puts it in a report, but does not save that value to the database.

So I am not sure how I would get that to work.
0
 
LVL 16

Accepted Solution

by:
wykabryan earned 500 total points
ID: 22743397
correct.  
You created a formula to comeup with the datediff. It is named something. Now in the report, create a new formula. Call it whatever, copy the above formula into the formula editor. Where it says  {YourTable.TotalSeconds} you will put you made to come up with the seconds or you could just do the datediff in the conversion like so..

WhilePrintingRecords;
NumberVar TotalSec :=  datediff("s",date1,date2); // or put the name of your datediff formula here
NumberVar Days    := Truncate  (TotalSec / 86400);
NumberVar Hours   := Truncate  (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate  (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;
Totext ( Days ,    '##' ) +  ':' +
Totext ( Hours ,   '00' ) +  ':' +
Totext ( Minutes , '00' ) +  ':' +
Totext ( Seconds , '00' )
0
 

Author Closing Comment

by:riley00
ID: 31507181
That is great, thanks and sorry for the confusion.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

690 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