Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Technical Questions
Message Icon Topic: DateDiff returning wrong value Post Reply Post New Topic
Page  of 2 Next >>
Author Message
huddy33
Groupie
Groupie


Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
Quote huddy33 Replybullet Topic: DateDiff returning wrong value
    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!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9011
Quote DBlank Replybullet 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


Edited by DBlank - 15 May 2012 at 10:59am
IP IP Logged
huddy33
Groupie
Groupie


Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
Quote huddy33 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9011
Quote DBlank Replybullet Posted: 15 May 2012 at 11:14am
can you please explain with more exact specificity what you want calculated with your extra conditions?

Edited by DBlank - 15 May 2012 at 11:15am
IP IP Logged
huddy33
Groupie
Groupie


Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
Quote huddy33 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9011
Quote DBlank Replybullet 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...?


Edited by DBlank - 15 May 2012 at 11:48am
IP IP Logged
huddy33
Groupie
Groupie


Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
Quote huddy33 Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9011
Quote DBlank Replybullet 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...

Edited by DBlank - 15 May 2012 at 2:32pm
IP IP Logged
huddy33
Groupie
Groupie


Joined: 23 Apr 2009
Location: United States
Online Status: Offline
Posts: 54
Quote huddy33 Replybullet 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.
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet 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.
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.