solution to DateDiff rounding error
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8396
Printed Date: 15 May 2024 at 5:55pm
Topic: solution to DateDiff rounding error
Posted By: Cordellv
Subject: solution to DateDiff rounding error
Date Posted: 18 Nov 2009 at 8:44am
|
We have always used this formula to know how old a person is on a given day:
truncate(DateDiff("d",{?prmBirthdate},{?Effective Date})/365,0) However we recently discoverd that there is a rounding error and if the report is run the day before a person's birthday after about age 3 it starts to be off by .0027 and then finally by one full day (an then there is Feb 29th in leap years).
So here are two formulas that we found that work without that error:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Cordell
------------- Making Things Better One Day At A time |