Solved

Crystal Reports datediff

Posted on 2008-10-17
8
749 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 100

Expert Comment

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

mlmcc
0
 
LVL 100

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
Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 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