Hi Michael ,

WOW.  I didn't expect such a support, with code, for this thread.   Thanks for your valuable time Miguel Leeuwe and Michael Krammer.   

I gave, Miguel Leeuwe's code, for testing to my client and the client has asked us to incorporate it in application.  With his initial testing he is OK with the result(s).  We are proceeding to incorporate in our real application.   

Thanks once again.   

Happiness Always
BKR Sivaprakash

Hi Siva, here is code-sample capable of calculating "age" = duration from "birth date" to "query date".
Code segments

1. FUNCTION of_dayInPriorMonth -- helper to the next function - treats month for its days.
   EXAMPLE: of_dayInPriorMonth ( 2016, 03, 05 ) returns ( 2016, 02, 34 )
2. FUNCTION of_Calculate_AGE -- Returns as REF parameters age as years/months/days.
3. FUNCTION of_TEST -- Example of calling code. (1) and (2) are functions on n_age_calculator.

 REVISION #2
FIX #1: "birth" date before "query" date gave undesired result. ==> Fixed by "intro" block in of_Calculate_Age to handle negative age.
FIX #2: Prefix function calls by "this." ==> Avoid mix-up when someone adds same named global functions.
NOTE: REF prefix highlights every pass-by-REF argument!
(Compiler ignores REF and therefore allows it in front of any argument)

// FUNC of_dayInPriorMonth(REF al_day, REF al_month, REF al_year)
// Converts <day, month, year> into <day+N, month2, year2>
// Where "N" represents number of days in <month2, year2>
// Having <month2, year2> = month before <month, year>
long daysInMonth[12] = {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31}
long moreDays

al_month --
if al_month = 0 then
   al_year --;   al_month = 12
end if

moreDays = daysInMonth[al_month]

// Adjust for Leap in February only
if al_month = 2 then
   choose case true
      case Mod(al_year, 400) = 0;  moreDays ++
      case Mod(al_year, 100) = 0;  // Not leap
      case Mod(al_year,   4) = 0;  moreDays ++
      case else;                   // Not leap
   end choose
end if
al_day += moreDays

// FUNCTION of_Calculate_Age( date ad_birth, date ad_query, ...
// ... REF al_ageYears, REF al_ageMonths, REF al_ageDays)

// Ensure correct handling of "birth after query"
if ad_birth > ad_query then
   // Negative age => Negate "reverse" age
   this.of_Calculate_Age(ad_query, ad_birth, REF al_ageYears, REF al_ageMonths, REF al_ageDays)
   al_ageYears  *= -1
   al_ageMonths *= -1
   al_ageDays   *= -1
   return // DONE
end if

long birthDay, birthMonth, birthYear
long queryDay, queryMonth, queryYear

birthYear  = Year(ad_birth);    queryYear  = Year(ad_query)
birthMonth = Month(ad_birth);   queryMonth = Month(ad_query)
birthDay   = Day(ad_birth);     queryDay   = Day(ad_query)

do while birthDay > queryDay
   // Convert "incomplete month" into days
   this.of_dayInPriorMonth(REF queryYear, REF queryMonth, REF queryDay)
loop

// Convert "incomplete year" into months
if birthMonth > queryMonth then
   queryYear --; queryMonth += 12
end if

al_ageYears  = queryYear  - birthYear
al_ageMonths = queryMonth - birthMonth
al_ageDays   = queryDay   - birthDay

// FUNCTION of_TEST( )
long days, months, years

date birthDate = Date(1219, 06, 15) // The Danish national flag's birthdate
date queryDate = Date(2020, 01, 02)

n_age_calculator lnv_age
lnv_age = create n_age_calculator

lnv_age.of_Calculate_Age(birthDate, queryDate, REF years, REF months, REF days)

string ls_title, ls_text
ls_title = string(birthDate,"yyyy.mm.dd") + " - " + string(queryDate, "yyyy.mm.dd")
ls_text  = string(DaysAfter(birthDate, queryDate)) + " Days~r~n"
ls_text += string(years) + " years, " + string(months) + " months, " + string(days) + " days."
MessageBox(ls_title, ls_text)

TIPS:

• Use LONG to avoid arithmetic overflow (max "int" = approx. 90 years).
• Documented valid year range in PowerScript is 1000 - 3000.
• It seems to me dates calculate correctly in rang 0001 - 9999.
• The only chance of the loop running more than once is when birthdate is January, 30th -31st and query date is March, 1st - 2nd.

HTH /Michael

Hi,

Me and Matthew pointed out that maybe one way would be to use DB functions, but you say you want your solution to be DB independent.

So ... if your end users all have excel, maybe that might be another possible solution (using OLE):

https://www.lifewire.com/calculate-current-age-with-excel-datedif-function-4178612

Careful though: someone pointed out that in Excel (maybe an old version?) all months seem to be counted as 31 days:

https://excelsemipro.com/2011/01/how-many-years-months-and-days-has-it-been/

So .. my solution is not the only one that "sucks" haha!

Good luck!

You need to specify your rules for date/time/duration rules precisely before code calculations an be verified. LIKE:

What is duration from Friday 23:59 to following Monday 00:01 ?
I would argue 2 days, 2 minutes.
In Danish tax laws this is 4 calendar days = almost 100% rounding up!

SO many different rule sets - hence so many different implementations.

When handling time zones and daylight savings time, I follow these steps (local datetimes can be in different time zones):

1. Convert Local => UTC
2. Calculate
3. Convert back UTC => Local

Okay here's my hopefully final version, see attached.

Some notes:

Since this age calculation is based upon date and not datetime, if for example, a baby is born today 5 minutes before midnight, then 5 minutes after midnight, the calculation will show 1 day.

Months are only counted, if the day of the current date equals or has past the day of the date of birth:

An example:

Today is 22/11/19. If a baby was born on 23/10/19, the month value will be 0 and the days will be 30.

I hope if it's any good, it's more complicated as it seems at first sight.

regards, get well 

MiguelL

The month calculation needs to be adapted to:

li_months = lnv_date.of_monthsAfter(ld_start, ld_today)
li_months = mod(li_months, 12) // take out the years
li_months = int(li_months) // int() returns the largest whole number less than or equal to n

That will result in:

Years: 19

Months: 10

Days: 21

When specifying 01/01/2000 as "start date", which I should have called "Date of Birth".

I'll do some more testing and upload a new version later on.

regards.

Okay, since it was fun to do, I've made a small sample app for you, for which I've re-used some PFC functionallity.

See attached. I'm not saying it's perfect, but it helps you on the way.

Regards,

MiguelL

NOTE: I've deleted the attachment as there's a little bug :s

Will upload new zip later on the day.

You may also want to consider using a database query (stored procedure) to do this via the 'DATEDIFF' method.

Not sure if perfect but ... from a simple google: http://codeverge.com/sybase.powerbuilder.general/calculating-number-of-months-and/1030208

The number of days can be calculated with the PB function RelativeDate. The 
number of months can be calculated with the PFC function of_RelativeMonth 
(pfc_n_cst_datetime).

Maybe also you can use some DB functions to achieve the results. On which database are you?