Hi
What is the embed formula to calculate the age of a person whos birthday is on the 29th February. With normal formula I get 20years old.
Kind regards
I don’t know that the 29th Feb would make any difference? Well apart from deciding if in non-leap years you advance their age on 28th Feb or 1st March.
what date were they born and what “normal formula” are you using? IOW please show your code.
1 Like
that was hilarious Geoff B.
Tony, off the top of my head I think something simple like this will work
AgeInYears long,auto
DateOfBirth long ! some clarion date
code
AgeInYears = year(today()) - year(DateOfBirth)
if month(dateOfBirth) > month(today()) or |
(month(dateOfBirth) = month(today()) and |
day(dateOfBirth) > day(today()))
AgeInYears -= 1
end
also see this earlier thread:
1 Like
@Mark_Sarson On my phone finding this new editor, that is WYSIWYG, hard to use. Can I switch to the old way? I.e. a way to edit the Markdown? A way to enter the 3 backticks where I want them.
I think your code needs to subtract 1 year when Less Than
EDIT: below code is correct. See post below…
AgeInYears long,auto
DateOfBirth long ! some clarion date
code
AgeInYears = year(today()) - year(DateOfBirth)
if month(dateOfBirth) < month(today()) or |
(month(dateOfBirth) = month(today()) and |
day(dateOfBirth) < day(today()))
AgeInYears -= 1
end
I think my code from the other thread post 14 that uses Date() serial numbers does work:
AgeInYears PROCEDURE(LONG DOB)!,SHORT
AgeNow SHORT,AUTO
YearNow SHORT,AUTO
DateThisYear LONG,AUTO
CODE
YearNow=Year(TODAY())
AgeNow =YearNow - Year(DOB)
DateThisYear = DATE(Month(DOB),DAY(DOB),YearNow)
IF TODAY() < DateThisYear THEN !Has not reached BDay of this year
AgeNow -= 1
END
RETURN AgeNow
2/29 is Julian date 31+29=60 in leap years
3/1 is Julian date 31+28+1=60 in regular years
Date(2,29,2025) will calculate the same serial number (Julian like date) as Date(3,1,2025) in non-leap years. Back in c5 I think it did not so I made a DateFixed() function.
I have this Date Time Tool on GitHub that does have a Date Calculations tab that let’s you test invalid Date() function parameters.
Hmm - I was about to change it (because you are almost always right!) but then I decided it was correct as it was. Can you double check please Carl?
On windows there is a “M” with a downwards arrow for MarkUp or MarkDown or MarkSarson 
not sure if there is something similar on your phone?
well one of us is having a brain fart. I’m just not sure which one of us
consider my original code:
what that says is
a) find difference in years
b) if the person has not yet had their birthday this year, then subtract 1.
If we don’t end up agreeing I might have to bite the bullet and test something for once!
@CarlBarnes top left on the toolbar?
1 Like
My mistake, I failed to read your code.
I think and code IF Today < Birthday Then -=1
But you flipped it to IF Birthday > Today Then -=1
So the Leap question… if birth is 2/29 and it’s 3/1 your code is
IF 2 > 3 OR (2=3 AND 29>1) THEN Age -=1
The IF is all false because Birthday has passed so Age is not reduced. In short it works.
I kind of like my code for having less functions, and my preferred thinking of IF Today is Before Birthday. I’d revise to make Today a passed value LONG TodaysDate=0 as its faily common to want to know on a specific future or past date.
AgeInYears PROCEDURE(LONG DOB, LONG TodaysDate=0)!,SHORT
AgeNow SHORT,AUTO
YearNow SHORT,AUTO
BDayThisYear LONG,AUTO
CODE
IF TodaysDate < 4 THEN TodaysDate=TODAY().
YearNow = Year(TodaysDate)
AgeNow = YearNow - Year(DOB)
BDayThisYear = DATE(Month(DOB),DAY(DOB),YearNow)
IF TodaysDate < BDayThisYear THEN !Has not reached BDay of this year
AgeNow -= 1
END
RETURN AgeNow
You say TOMAYTO, I say TOMARTO
or
“Six of one, half a dozen of the other”
Tony, if you are still following along at home. here is the alternative take:
AgeInYears long,auto
DateOfBirth long ! some clarion date
code
AgeInYears = year(today()) - year(DateOfBirth)
if month(today()) < month(dateOfBirth) or |
(month(today()) = month(dateOfBirth) and |
day(today()) < day(dateOfBirth))
AgeInYears -= 1
end
it would be interesting to know which version most people find more intuitive, and if it is just one of those personal preference things like code indenting… and yes your way of explicitly working out this year’s birthday for comparison is yet another way to go.
cheers for now
Geoff R
Hi Geoff,
Does that give a different result to AGE(StartDate,Enddate) ?
yes a different result. My code was for age in years. The Age() function does different units like days or months, depending on age.
A SQL function I’ve used for many years:
CREATE FUNCTION [dbo].[fnAgeAsOf] (@BirthDate DATE, @AsOfDate DATE)
RETURNS INT
AS
BEGIN
DECLARE @Age SMALLINT;
SET @age=DATEDIFF(yy, @birthdate,@asofdate);
SET @birthdate = DATEADD(yy, @age, @birthdate);
IF @birthdate>@AsOfDate
SET @Age=@Age-1;
RETURN @age;
END;
GO
and if you want to reduce all the function calls you could use a few extra variables:
AgeInYears long,auto
DateOfBirth long ! some clarion date
tdy long,auto ! today
mthTdy byte,auto ! month today
mthDOB byte,auto ! month of date of birth
code
tdy = today()
mthTdy = month(tdy)
mthDOB = month(DateOfBirth)
AgeInYears = year(tdy) - year(DateOfBirth)
if mthTdy < mthDOB or |
(mthTdy = mthdDOB and day(tdy) < day(dateOfBirth))
AgeInYears -= 1
end
#edit1 - added auto to variables
you can decide which you think is better
As compared to nested IF conditions with Month and Day my way of having the entire Birth DATE() allows one simple IF and doing more like calculating the Decimal portion of the Age with code like this (untested):
AgeInYearsREAL PROCEDURE(LONG DOB, LONG TodaysDate=0)!,REAL
AgeYears SHORT,AUTO
AgeFraction REAL,AUTO !1/366=.00273 amd 365/366=.99726
YearNow SHORT,AUTO
LastBirthDay LONG,AUTO
NextBirthDay LONG,AUTO
DaysInYear LONG,AUTO !365 or 366
DaysSinceBDay LONG,AUTO !Today - Last Birthday
CODE
IF DOB < 4 THEN RETURN 0. !Invalid DOB
IF TodaysDate < 4 THEN TodaysDate=TODAY().
YearNow = Year(TodaysDate)
AgeYears = YearNow - Year(DOB)
LastBirthDay = DATE(Month(DOB),DAY(DOB),YearNow)
IF TodaysDate < LastBirthDay THEN
!Has NOT reached BirthDay in THIS year
AgeYears -= 1
NextBirthDay = LastBirthDay
LastBirthDay = DATE(Month(DOB),DAY(DOB),YearNow-1)
ELSE !Has reached Birthday THIS Year
NextBirthDay = DATE(Month(DOB),DAY(DOB),YearNow+1)
END
DaysInYear = NextBirthDay - LastBirthDay ! 365 or 366 (leap)
DaysSinceBDay = TodaysDate - LastBirthDay !0 to 364 or 365 (leap)
AgeFraction = DaysSinceBDay / DaysInYear !0 or .0027 to .9972
RETURN AgeYears + AgeFraction
I declared some extra variables, one for each calculation. Several of these numbers may be useful, but rather that have several Out* variables this would make a good Birthday CLASS that could have many methods.
Instead of all the YEAR(), MONTH(), DAY() function calls below is an example of using a DATE type that an OVER() gets those 3 values:
AgeInYearsREAL PROCEDURE(LONG DOB, LONG TodaysDate=0)!,REAL
DobSplit DATE,AUTO
DobGroup GROUP,OVER(DobSplit)
DobDay BYTE !instead of DAY(DOB)
DobMonth BYTE !instead of MONTH(DOB)
DobYear USHORT !instead of YEAR(DOB)
END
AgeYears SHORT,AUTO !1/366=.00273 and 365/366=.99726
AgeFraction REAL,AUTO !so a Decimal() would need 3 places
YearNow SHORT,AUTO
LastBirthDay LONG,AUTO
NextBirthDay LONG,AUTO
DaysInYear LONG,AUTO !365 or 366
DaysSinceBDay LONG,AUTO !Today - Last Birthday
CODE
IF DOB < 4 THEN RETURN 0. !Invalid DOB
DobSplit = DOB
IF TodaysDate < 4 THEN TodaysDate=TODAY().
YearNow = Year(TodaysDate)
AgeYears = YearNow - DobYear
LastBirthDay = DATE(DobMonth,DobDay,YearNow)
IF TodaysDate < LastBirthDay THEN
!Has NOT reached BirthDay in THIS year
AgeYears -= 1
NextBirthDay = LastBirthDay !Next BDay is This Year
LastBirthDay = DATE(DobMonth,DobDay,YearNow-1)
ELSE !Has reached Birthday THIS Year
NextBirthDay = DATE(DobMonth,DobDay,YearNow+1)
END
DaysInYear = NextBirthDay - LastBirthDay
DaysSinceBDay = TodaysDate - LastBirthDay
AgeFraction = DaysSinceBDay / DaysInYear
RETURN AgeYears + AgeFraction
2 Likes
In Oracle it’s really too simple to bother with a function:
trunc(months_between(sysdate,birth_date)/12)
Yes but under the hood, Oracle performs :
-Date arithmetic using floating-point numbers internally for dates
-By executing underlying C code that handles numeric values for numbers
Many Clarion users have similar “trunc” functions for these use cases
If you are worried about efficiency, it is probably even worse than doing floating point number calculations. One of the lovely things about months_between is that the ends of the months are even numbers of months apart, so October 31 is exactly eight months later than Feb 28 (or Feb 29 in a leap year). That sort of ties back to the original question about February leap years. The Oracle function is not just a mathematical calculation.
On my database I can get the average age (doing that age calculation as pat of it) for 280,000 people in a half second, so really not much of a worry.