Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 784
  • Last Modified:

Crystal Reports datediff

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
riley00
Asked:
riley00
  • 3
  • 3
  • 2
1 Solution
 
mlmccCommented:
Are the fields dates, datetimes, or strings?

mlmcc
0
 
mlmccCommented:
Can you show the formula?

mlmcc
0
 
wykabryanCommented:
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
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

 
riley00Author Commented:
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
 
wykabryanCommented:
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
 
riley00Author Commented:
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
 
wykabryanCommented:
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
 
riley00Author Commented:
That is great, thanks and sorry for the confusion.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now