DateDiff returning wrong value
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=16435
Printed Date: 19 Apr 2024 at 1:58pm
Topic: DateDiff returning wrong value
Posted By: huddy33
Subject: DateDiff returning wrong value
Date Posted: 15 May 2012 at 10:17am
I have the following formula for calculating the difference between two dates. I need to run a calculation once I get the correct value.
DateDiff("d",{Meeting.TravelStartDate},{Meeting.TravelEndDate})
The date set for this particular report is 4/29/12 to 5/4/12. It spans two months and a weekend. The formula above is returning 5 when it needs to return 6. I've tried everything, please help!
|
Replies:
Posted By: DBlank
Date Posted: 15 May 2012 at 10:59am
|
if you always want to calculate this as 1 more day than the standard function then add 1 to the value...
DateDiff("d",{Meeting.TravelStartDate},{Meeting.TravelEndDate})+1
|
Posted By: huddy33
Date Posted: 15 May 2012 at 11:09am
|
I have tried that and while it works for the above date set, it's wrong for any set that doesn't span a weekend/month switch.
|
Posted By: DBlank
Date Posted: 15 May 2012 at 11:14am
|
can you please explain with more exact specificity what you want calculated with your extra conditions?
|
Posted By: huddy33
Date Posted: 15 May 2012 at 11:35am
|
I need the exact number of days between those two days displayed as an integer. The reg datediff works except with situations like the date set above. It throws it off somehow. I'm thinking it's bc of the month switch.
|
Posted By: DBlank
Date Posted: 15 May 2012 at 11:47am
|
I am confused
DateDiff("d",date(2012,5,1),date(2012,5,6)) returns 5 the same as
DateDiff("d",date(2012,4,29),date(2012,5,4)).
What is the difference here that you think should be happening? If you look at a calendar and count the days the same way you will get 5 in both instances...?
|
Posted By: huddy33
Date Posted: 15 May 2012 at 12:07pm
|
I need it to show as six days bc that is the actual day count. 29, 30, 1, 2, 3, and 4.
|
Posted By: DBlank
Date Posted: 15 May 2012 at 2:30pm
That would also make the other one six. First, second, third, fourth, fifth, and sixth.
Sorry, I just cannot see the difference in the two examples ranges I gave you. If you want it include the first day in the difference I understand, but I don't get why you do it in one instance and not the other...
|
Posted By: huddy33
Date Posted: 15 May 2012 at 3:16pm
|
I want it to catch each individual day every time. When I add the +1 to the date diff, it messes up every non-month switch. I just need it to be accurate.
|
Posted By: rkrowland
Date Posted: 15 May 2012 at 10:57pm
|
No it doesn't, DBlank is right.
Give us examples of other date ranges where it messes up on non-month switches.
29/4 - 4/5 - 30, 1, 2, 3 , 4 - 5
10/4 - 17/4 - 11, 12, 13, 14, 15, 16, 17 - 7
Your math is confused as you think becuase 17-10 = 7 it is giving you the correct number. Your datediff is working correctly no matter how you look at it! If you want your report to be accurate and include the first date as a day ie 6 day between the first date range, 8 is the correct answer for the second date range.
|
Posted By: huddy33
Date Posted: 16 May 2012 at 3:44am
Because of the way this data is used, we have to count each day separately, not as a day+day=1 day. Rkrowland, in your example of 4/10-4/17, it would be 10, 11, 12, 13, 14, 15, 16, 17, equaling 8 days that need to be charged.
I worked it out with a simple formula that is testing as accurate. I still don't understand the root of the problem, but oh well.
|
Posted By: rkrowland
Date Posted: 16 May 2012 at 4:50am
|
Exactly 8 days would need to be charged... but if you did a datediff on that date range it would return 7. ie you need to put +1 on the end of your datediff formula - What Dblank said in his first post.
|
Posted By: huddy33
Date Posted: 16 May 2012 at 5:16am
That is how I first approached this issue before coming here. The problem was that it was adding an extra day to programs where it didn't need to. I added in an if statement comparing the month(startdate) and all is well. Thanks to both of you for trying.
|
Posted By: DBlank
Date Posted: 16 May 2012 at 5:42am
|
you can add an if statement to use a datediff('m',start,end) to see if a month is straddled or even multiple months are straddled and use that result to alter the day diff result as desired.
however, what both Ryan and I were trying to get at is that your written description of the problem was not matching the math as we understand it, therefore we were suggesting a solution that seems to match the needed math. I am not saying your solution is wrong but am cautioning you that it does not seem to match your description of your desired result.
Datediff('d',start,end) will always calculate the same way regardless of straddling over months or years. By choosing to add an if statement you are not doing consistent comparisons of date ranges.
|
Posted By: huddy33
Date Posted: 16 May 2012 at 5:48am
|
I guess I don't really understand why the datediff is not calculating all of the days regardless. I know you say it is, but in the instances where it spans two months, it is missing a day. I realize my solution is not consistent, but the typical datediff is not yielding the "correct" result in this case.
|
Posted By: DBlank
Date Posted: 16 May 2012 at 5:56am
|
my point is that datediff does calculate consistently but your description of how you want to count things is inconsistent.
use a one calendar day comparison
4-30-12 to 5-1-2012 is 1 day using datediff but you want 2 days
5-1-2012 to 5-2-2012 is also 1 day using datediff and you want 1 day
that is not consistent
|
|