Solved

Crystal Reports datediff

Posted on 2008-10-17
8
741 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
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

20 Experts available now in Live!

Get 1:1 Help Now