Solved

Crystal Reports datediff

Posted on 2008-10-17
8
742 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
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 …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

929 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

10 Experts available now in Live!

Get 1:1 Help Now