Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: DateDiff has a rounding error Post Reply Post New Topic
Author Message
Cordellv
Newbie
Newbie
Avatar

Joined: 14 Jan 2009
Location: United States
Online Status: Offline
Posts: 35
Quote Cordellv Replybullet Topic: DateDiff has a rounding error
     Posted: 09 Nov 2009 at 8:00pm
Am I the first one to find that DateDiff has a rounding error? 
 
I tried to calculate the age of disabled children in a school by taking their birth date from the table and then entering an effective date of the report.  There is enough of a rounding error that by the age of 3 if your report date is the day before the day of the day of the birth date you will be off by 1 year.
 
Effective Date 11/18/2009
Birthdate         11/20/2007
Date Diff days 729/365 = 1.9973
Age rounds down to 1
 
Effective Date 11/19/2009
Birthdate         11/20/2007
Date Diff days 730/365 = 2.0000
age rounds to 2
They wont be 2 until the next day.
 
Effective Date 11/20/2009
Birthdate         11/20/2007
Date Diff days 731/365 = 2.0027
Age rounds to 2 but the calculation is .0027 off
 
 
By age 21 the rounding is off by 2 days.
 
Here is the forumla I used:
 
truncate(DateDiff("d",{NAME.BIRTHDATE},{?Effective Date})/365,0)
 
The childs birth date is in the NAME table in the BIRTHDATE field. I have the user input the Effective Date of the report.
 
Does anyone know a way to accurately display on a Crystal Report how old someone is on the effective date of a report when calculated from the persons birth date?
 
Your help would be greatly appreciated by all the school districts serving disabled children in the state. 
 
Cordell V
Making Things Better One Day At A time
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 10 Nov 2009 at 6:25am
datediff just compares dates, and does so in a simple manner. 
 
Actually, you are asking for the date diff in days, and then dividing by 365 and truncating...you are causing the rounding error, not datediff which is just reporting the number or days.  Truncate will always round down, especially with 0 decimal places.  You might try MRound or Round.
 
HTH
IP IP Logged
Cordellv
Newbie
Newbie
Avatar

Joined: 14 Jan 2009
Location: United States
Online Status: Offline
Posts: 35
Quote Cordellv Replybullet Posted: 10 Nov 2009 at 9:17am

Thanks HTH.  I will try that.  I was originally using Round and someone told me that truncate was better. I will try going back to round or MRound.  I will let you know if that fixes it.  Cvail
Making Things Better One Day At A time
IP IP Logged
Cordellv
Newbie
Newbie
Avatar

Joined: 14 Jan 2009
Location: United States
Online Status: Offline
Posts: 35
Quote Cordellv Replybullet Posted: 10 Nov 2009 at 10:26am
I get the same error with ROUND.  I have no clue how to use MRound after studying it for an hour in the books.  I also see that I have an additional error when there is a leap year.  I cant divide by 365 because there are 366 days in that year.
 

DateDiff("d",#2008-02-18#,#2009-02-18#)  LEAP YEAR

366

DateDiff("d",#2007-02-18#,#2008-02-18#)

365

DateDiff("d",#2006-02-18#,#2007-02-18#)

365

DateDiff("d",#2005-02-18#,#2006-02-18#)

365

DateDiff("d",#2004-02-18#,#2005-02-18#)  LEAP YEAR

366

 
So maybe if I can figure out how to tell when it is leap year I can just use the DateDiff number of days.  Maybe I could use a select with
Cae 1 <=365(366 in leap year) they are 1
Case 2 <= 730 (731 in leap year) they are 2
 
I am still stumped
 
Surely I am not the first one to ever write a formula to calculate how old some one is using DateDiff to count the days.  AM I?

Cordell
 
Making Things Better One Day At A time
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 10 Nov 2009 at 10:57am
The big question is when do you want to round?  Is someone 2 when they are 1 year 183 days? Or are they 2 when they are 1 3/4 or when they are within a week of their birthday?
 
The resulting formula is really based on when your user wants to round, after all, technically if you are 1yr 364days you are not 2.  Unfortunately, datediff for year says that 1 yr is the diff from 12/31/01 to 1/1/02. 
 
What you might want to do is compare today vs dateadd(year,x,birthdate), but that will just tell you if someone is over a certain age, not their age.
 
HTH
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Nov 2009 at 11:12am
what about:
 
datediff('yyyy',{@dob},currentdate)
-
(if datepart('y',currentdate)>=datepart('y',{@dob}) then 0 else 1)
IP IP Logged
Cordellv
Newbie
Newbie
Avatar

Joined: 14 Jan 2009
Location: United States
Online Status: Offline
Posts: 35
Quote Cordellv Replybullet Posted: 12 Nov 2009 at 2:44pm

Both of these are excellent suggestions.  I will try them both.  I also have discovered that leap year has 366 days so that has to be taken into account here.  I am determined to come up with a acurate formula that tells how old a person is TODAY.  We use that in the school system all the time.  If a student is in 9th grade that does not mean he or she is necessarly 14 or 15.  I will post the formula here when I get one that is 100% right on.  I am very close to that now.

Thanks for offering your opinions... all of you.  That is what makes this Forum so great.

Cordell
Making Things Better One Day At A time
IP IP Logged
Cordellv
Newbie
Newbie
Avatar

Joined: 14 Jan 2009
Location: United States
Online Status: Offline
Posts: 35
Quote Cordellv Replybullet Posted: 16 Nov 2009 at 3:18pm

Thanks to those of you who helped me with this...

 
These two formulas seem to work perfeclty if anyone else needs to use them:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

datediff('yyyy',{?prmBirthdate},{?Effective Date}) -

 

(if datepart('y',{?Effective Date})>=datepart('y',{?prmBirthdate}) then 0 else 1)

 

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

NumberVar CurrentYrBirthdateVar := IIF(100 * month({?Effective Date}) + day({?Effective Date}) < 100 * month({?prmBirthdate}) + day({?prmBirthdate}),1,0);

 

NumberVar AgeAsofEffectiveDate := DATEDIFF("yyyy",{?prmBirthdate},{?Effective Date}) - CurrentYrBirthdateVar;

 

AgeAsofEffectiveDate;

 
ClapClapClapClap

This just goes to show one more time that friends are worth more than gold.... Cordell
Making Things Better One Day At A time
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.