## Resolved To calculate difference between two days in Year, Month, Days

0
Undo
1. Sivaprakash BKR
2. PowerBuilder
3. Tuesday, 19 November 2019 06:19 AM UTC

Hello,

IS there a way to calculate the difference between two dates in years, months, days taking into leap year also into consideration ?

Happiness Always
BKR Sivaprakash

Responses (10)
Jure Gornjak Accepted Answer Pending Moderation
1. Wednesday, 27 November 2019 16:35 PM UTC
2. PowerBuilder
3. # 1

Hi Michael ,

What is the result of your age calculation solution for the dates 25.11.2019 to 01.03.2021 ?

The date format is dd.mm.yyyy

Thank you and best regards

Comment
1. Michael Kramer
2. Wednesday, 27 November 2019 17:14 PM UTC
1 year, 3 months, and 4 days.

Calendar calculation isn't "commutative" (in layman's terms = order matters) so you need to add 1:Years, 2:Months, and finally 3:Days.

( ( (2019.11.25 + 1 year) + 3 months) + 4 days) = ( (2020.11.25 + 3 months) + 4 days) = (2021.02.25 + 4 days) = 2021.03.01.

1. Jure Gornjak
2. Wednesday, 27 November 2019 18:09 PM UTC
Thanks Michael,

On the website https://www.calculator.net/age-calculator.html

is an Age Calculator program that calculates an incorrect value

1 year 3 months and 6 days !

Some 20 years ago, I wrote a PB program, but it does

1 year 3 months and 4 days !
Sivaprakash BKR Accepted Answer Pending Moderation
1. Monday, 25 November 2019 10:31 AM UTC
2. PowerBuilder
3. # 2

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

Comment
1. Miguel Leeuwe
2. Monday, 25 November 2019 12:18 PM UTC
No worries Sivaprakash. Glad we could help.
Michael Kramer Accepted Answer Pending Moderation
1. Friday, 22 November 2019 21:06 PM UTC
2. PowerBuilder
3. # 3

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"
// Negative age => Negate "reverse" age
al_ageYears  *= -1
al_ageMonths *= -1
al_ageDays   *= -1
return // DONE
end if

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

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

Comment
1. Jure Gornjak
2. Sunday, 1 December 2019 19:33 PM UTC
Hello PB enthusiasts,

In my opinion (and my algorithm) the age is between

08/31/1993 and 03/01/2019 is:

25 years 6 months and 1 day!
1. Jure Gornjak
2. Sunday, 1 December 2019 19:44 PM UTC
I apologize, I thought ,

In my opinion (and my algorithm) the age is between

31/08/1993 and 01/03/2019 is:

25 years 6 months and 1 day!
1. Michael Kramer
2. Sunday, 1 December 2019 19:58 PM UTC
It all depends on how you interpret "illegal" dates in intermediate results.

If you add just 25 years and 6 months to 31-Aug-1993 - What date do you get?

Your calculation if I interpret correctly moves you to 28-Feb-2019. I argue that is 3 days prior. I therefore suggest:

FIRST :: you add 25 years and 5 months moving you to 31-Jan-2019 (fully legal date).

SECOND :: You add 29 days. The first 28 days moves you to 28-Feb-2019. The 29th day moves you cross the finish line to 01-Mar-2019.
Miguel Leeuwe Accepted Answer Pending Moderation
1. Friday, 22 November 2019 15:25 PM UTC
2. PowerBuilder
3. # 4

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!

Comment
1. Michael Kramer
2. Friday, 22 November 2019 16:20 PM UTC
With C# Interop becoming easier and faster with PB 2019 R2 it is also worth consider well-known time processing packages like Noda Time (https://nodatime.org/).
1. Miguel Leeuwe
2. Friday, 22 November 2019 16:22 PM UTC
Nice one!
Michael Kramer Accepted Answer Pending Moderation
1. Friday, 22 November 2019 09:48 AM UTC
2. PowerBuilder
3. # 5

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

Comment
1. Miguel Leeuwe
2. Friday, 22 November 2019 11:15 AM UTC
yes, but like Rene also said, they are many times "personal".Great advice though.
1. Miguel Leeuwe
2. Friday, 22 November 2019 11:50 AM UTC
Seems like my day count follows Danish rules :) But all the other problems exist in my code, so Sivaprakash has to figure that out himself.
1. Sivaprakash BKR
2. Friday, 22 November 2019 14:56 PM UTC
I agree. With time, the things becomes more complex. That's why I removed that part in my query itself. Let me see the working of the Miguel Leeuwe. If that's agreeable to my client, hope the things could get solved, I think.
Miguel Leeuwe Accepted Answer Pending Moderation
1. Friday, 22 November 2019 06:28 AM UTC
2. PowerBuilder
3. # 6

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

Attachments (1)
Comment
1. Miguel Leeuwe
2. Friday, 22 November 2019 22:29 PM UTC
Hi Michael,

I'm getting 800 years, 5 months, 7 days comparing your flag date with today 22/11/19.

So how's that different to what you get?

1. Michael Kramer
2. Friday, 22 November 2019 22:31 PM UTC
FOUND REASON - Edit Mask has format dd/mm/yyyy but my regional settings has format = yyyy-mm-dd.

I enter 15/06/1219 and app reads that as 2015-06-12. Calculations are correct!

FIX => Use edit mask = [date]

That edit mask applies Windows' regional settings "short date" format to the edit mask.
1. Miguel Leeuwe
2. Friday, 22 November 2019 22:32 PM UTC
Yeah, that explains. I first used [shortdate] instead of hardcode mask of dd/mm/yyyy but can't remember what problem I ran into and then considered since author's country probably is India their dateformat is probably British. Lots of assumptions I maybe should not make.
Miguel Leeuwe Accepted Answer Pending Moderation
1. Friday, 22 November 2019 05:55 AM UTC
2. PowerBuilder
3. # 7

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.

Comment
1. Sivaprakash BKR
2. Friday, 22 November 2019 06:56 AM UTC
Still I haven't checked your code.

As per your project, it seems, you have get input of only one date. So I presumed that you calculate difference between start date and today. So in your case the date of birth is start date.
1. Miguel Leeuwe
2. Friday, 22 November 2019 10:59 AM UTC
Yes, I've renamed the field to "Date of Birth". There's only one Input field, because if you want to express AGE, mostly you want to know the AGE today. For testing all the funny cases though, it would be nice to indeed add a second input field on the window and use that value instead of Today().
Miguel Leeuwe Accepted Answer Pending Moderation
1. Tuesday, 19 November 2019 11:39 AM UTC
2. PowerBuilder
3. # 8

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.

Comment
1. Sivaprakash BKR
2. Friday, 22 November 2019 05:25 AM UTC
Michael Kramer,

I want difference between 2 dates, not datetime. So those complexities is not there in my requirement.

I just want to display

Your age is : 37 years, 10 months, 21 days.

That's all.

HTH.

1. Miguel Leeuwe
2. Friday, 22 November 2019 05:29 AM UTC
Ups! That's a bug, I'll take a look later on, have to sleep a bit, it's 5:30 AM here.
1. Michael Kramer
2. Friday, 22 November 2019 19:58 PM UTC
Hi SIva,

If you want to know someone's age you actually ask for "duration" = distance in time between "today" and somebody's birthday. I need to do this with syntax for easier reading so a new reply will turn up in a few minutes.

Matthew Balent Accepted Answer Pending Moderation
1. Tuesday, 19 November 2019 11:34 AM UTC
2. PowerBuilder
3. # 9

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

Comment
Miguel Leeuwe Accepted Answer Pending Moderation
1. Tuesday, 19 November 2019 07:55 AM UTC
2. PowerBuilder
3. # 10

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? ```
Comment
1. Sivaprakash BKR
2. Tuesday, 19 November 2019 08:39 AM UTC
I'm currently using PostgreSQL. The solution should be independent of Database.
1. Sivaprakash BKR
2. Tuesday, 19 November 2019 08:45 AM UTC
Also I'm not aware of what PFC functions do. Don't have any idea on PFC and its functions. Never used it.
1. Miguel Leeuwe
2. Tuesday, 19 November 2019 08:51 AM UTC
- There are date functions in databases which might be an alternative way of calculating your numbers, using a datastore or dynamic sql, but it's okay if you don't want to use that and want to use only code.

- PFC classes are here: https://github.com/OpenSourcePFCLibraries they're used in many applications. Even if you don't use them, they still have a lot of useful code.