CODEWITHSUNDEEP
crystal-reports
user2895535
user2895535

Reputation: 1

how do i get duration of time from datetime in crystal report

I am using crystal reports and im trying to make a duration of how long it took me to a task. The report currently pulls from SQL a accepted datetime and completed datetime.

What im trying to make is a field where you can see how many months, days, hours, minutes, and seconds it took me to complete the task. The way it is being saved in SQL is 01/01/1901 00:00:00 for example on Accepted and Completed fields. I currently have built is this below but it is not pulling the information accuratly. Can someone help please. Thanks for your help in advance.

StringVar varMonth := ToText(DatePart("m",{PROJECT_MGMT.STATUS_UPDATE_DATE}) - DatePart("m",{PROJECT_MGMT.PROJECT_ASSIGN_DATE}),0);
StringVar varDay := ToText(DatePart("d",{PROJECT_MGMT.STATUS_UPDATE_DATE}) - DatePart("d",{PROJECT_MGMT.PROJECT_ASSIGN_DATE}),0);
StringVar varHour := ToText(DatePart("h",{PROJECT_MGMT.STATUS_UPDATE_DATE}) - DatePart("h",{PROJECT_MGMT.PROJECT_ASSIGN_DATE}),0);
StringVar varMinute := ToText(DatePart("n",{PROJECT_MGMT.STATUS_UPDATE_DATE}) - DatePart("n",{PROJECT_MGMT.PROJECT_ASSIGN_DATE}),0);
StringVar varSecond;
NumberVar fff := DatePart("s",{PROJECT_MGMT.STATUS_UPDATE_DATE}) - DatePart("s",{PROJECT_MGMT.PROJECT_ASSIGN_DATE}) + 60;
if (fff >= 60) Then
fff = fff - 60;
varSecond = ToText(fff);
varMonth + " Months " + varDay + " Days " + varHour + " Hours " + varMinute + " Minutes " + ToText(fff) + " Seconds "

Upvotes: 0

Views: 3279

Answers (1)

campagnolo_1
campagnolo_1

Reputation: 2750

In your Crystal report, create 5 formulas, one for each months, days, hours, minutes, seconds. Code would be the same for all, except the Interval type value ("m", "d", "h", "n", "s"):

cStr(DateDiff("m", AcceptDate, CompleteDate),0)

The conversion to string is needed to get rid of the decimals. Then you create another formula where you put them all together, something like:

Formula1 + " months," + Formula2 + " days," + Formula3 + " hours," + ......

You get the idea.

Hope that helps,

Chris

EDIT: Sorry, I realized after I wrote this that it won't work because it will show the total of months and days, etc. So maybe you need to calculate the difference. Give me some time to figure that one out.

EDIT 2: Ok, so I think the way you can do this is by using the following kind of formula:

year(completeDate)- year(acceptDate)

will give you the year. You do the same for months, days, hours, etc. Then you put them all together in a seperate formula. The only caveat is that if you span a year, but not 12 months. Say 10/2013 to 02/2014. So you will have to put in an "if" statement that checks whether the months is greater than 12 if the year is greater than 1. Do you get the idea?

Upvotes: 0

Related Questions