Calculate difference between two dates?

anil_mujagic

Does anybody have an idea how to calculate the difference between two dates?
I want to calculate the difference and get the result in form of three variables which will represent years, months, days.

I need this to be able to show how long does an employee work for the company.
That information will be presented in this form: YY-MM-DD

SteveO

You can perform subtraction on 2 dates to get the number of days elapsed between them

numdays := Date1 - Date2;

numdays is an integer and Date1 and Date2 are both date variables.

From the number of days you can then calculate the number of years, months and days.

DenSter

There's a data type 'duration' you might want to look at. I haven't used it myself, but that's where I'd start.

anil_mujagic

Well, it's a little bit complicated than it seams...
If I start with difference in days I'll need to take care of leap years.

What is the result that I need:

Start date: 17.07.2004
End date: 13.02.2007

Difference should be (YY-MM-DD): 02-06-27

So I decided to start this way:

y1 := DATE2DMY(StartDate, 3);
m1 := DATE2DMY(StartDate, 2);
d1 := DATE2DMY(StartDate, 1);

y2 := DATE2DMY(EndDate, 3);
m2 := DATE2DMY(EndDate, 2);
d2 := DATE2DMY(EndDate, 1);

m := (y2 - y1) * 12;     
m := m + (m2 - m1);

g := m DIV 12;
m := m MOD 12;

// Didn't handle days yet

MESSAGE(CONVERTSTR(FORMAT(g, 2) + '-' + FORMAT(m, 2) + '-' + FORMAT(d, 2), ' ', '0'));

To handle days is a little bit more complicated than what I did to this point, so if somebody has an idea, please drop it here

TNX!

anil_mujagic

Or... if I'm on the wrong path feel free to show me the right path.

kine

It depends on what you are expecting. Because if you want to have difference in YY-MM-DD you need to say what factors you are using:

1Y=xM
1M=yD

Because one Month can be 30,31,28,29 days and it has no meaning to calc difference in such a unit...

anil_mujagic

This is what I need...

http://cs.senecac.on.ca/~tmckenna/bobje ... anCalendar)

masch

try this code:

StartDate := 170704D;
EndDate   := 130207D;

recDate.RESET;
recDate.SETRANGE("Period Type",recDate."Period Type"::Day);
recDate.SETRANGE("Period Start",StartDate,EndDate);

TotalDays := RecDate.COUNT;          // only for information

IF RecDate.FINDSET THEN BEGIN

  LastFoundDate := StartDate;

  // *** find count of years ***
  TempDate := CALCDATE('+1Y',StartDate);
  Found := TRUE;

  REPEAT
    IF (TempDate <= EndDate) AND (RecDate.GET(RecDate."Period Type"::Day,TempDate)) THEN BEGIN
      CountYears += 1;
      LastFoundDate := TempDate;
      TempDate := CALCDATE('+1Y',TempDate);
    END ELSE
      Found := FALSE;
  UNTIL NOT Found;

  // *** find count of months ***
  TempDate := CALCDATE('+1M',LastFoundDate);
  Found := TRUE;

  REPEAT
    IF (TempDate <= EndDate) AND (RecDate.GET(RecDate."Period Type"::Day,TempDate)) THEN BEGIN
      CountMonths += 1;
      LastFoundDate := TempDate;
      TempDate := CALCDATE('+1M',TempDate);
    END ELSE
      Found := FALSE;
  UNTIL NOT Found;

  // *** find count of days ***
  TempDate := CALCDATE('+1D',LastFoundDate);
  Found := TRUE;

  REPEAT
    IF (TempDate <= EndDate) AND (RecDate.GET(RecDate."Period Type"::Day,TempDate)) THEN BEGIN
      CountDays += 1;
      LastFoundDate := TempDate;
      TempDate := CALCDATE('+1D',TempDate);
    END ELSE
      Found := FALSE;
  UNTIL NOT Found;
END;

MESSAGE(Text001,TotalDays,CountYears,CountMonths,CountDays);

I've used these variables and types:

RecDate Record Date
StartDate Date
EndDate Date
TempDate Date
LastFoundDate Date
TotalDays Integer
CountYears Integer
CountMonths Integer
CountDays Integer
Found Boolean

Text001 Total Days: %1\Years: %2\Months: %3\Days: %4

After running this code (with your example dates) I get the message:
Total Days: 942
Years: 2
Months: 6
Days: 27

The count of days per month (28,29,30,31) shouldn't be a problem, because Navision-Table DATE knows ist.

Hope this is helpful.

DenSter

Scratch the duration, that seems to give milliseconds, so that would not be much help.

I don't see how you can come up with a good solution for that. Like Kamil said, since months and even years have different numbers of days, a YY-MM-DD expressed as a number of years plus a number of months plus a number of days would be meaningless without a starting date.

How were you going to address that part?

anil_mujagic

Hehe \:D/ I think I found the right solution...

IF StartDate > EndDate THEN BEGIN
  MESSAGE(textDateError);
  EXIT('00-00-00');
END;

g1 := DATE2DMY(StartDate, 3);
m1 := DATE2DMY(StartDate, 2);
d1 := DATE2DMY(StartDate, 1);
m1days := LastDayOfMonth(g1, m1);    // Simple function to find last day of month using Date table

g2 := DATE2DMY(EndDate, 3);
m2 := DATE2DMY(EndDate, 2);
d2 := DATE2DMY(EndDate, 1);

IF d2 < d1 THEN BEGIN
  d2 += m1days;
  m2 -= 1;
END;
d := d2 - d1;

IF m2 < m1 THEN BEGIN
  m2 += 12;
  g2 -= 1;
END;
m := m2 - m1;

g := g2 - g1;

EXIT(CONVERTSTR(FORMAT(g, 2) + '-' + FORMAT(m, 2) + '-' + FORMAT(d, 2), ' ', '0'));

I just need to test it a little bit but I think this is it.

kine

The definition of one month is there:

Differences take month end dates and shorter/longer months into account:
- whole months are always counted were possible.
- If both dates are at month end, the difference is in whole months only; the days difference will be zero regardless of the differing lengths of months. e.g. the intervals between Jan.31, Feb.28, Mar.31, and Apr.30 are all 1 month and 0 days.
- If the oldest date is at or near the end of the month, whole months are counted up to the most recent date and then days. e.g.
from Jan.28/29/30/31 to Feb.28 is 1 month and 0 days
from Mar.30/31 to Apr.30 is 1 month and 0 days
from Apr.30 to May 31 is 1 month and 0 days
from Feb.28 to March 30 is 0 month and 30 days, not 1 month and 2 days
from Apr.30 to May 30 is 0 month and 30 days, not 1 month and 0 days
from Feb.28 to April 28 is 1 month and 28 days (from Feb. Month End to March Month End = 1 mo. + 28 days)
from Mar.30/31 to May 27 is 1 month and 27 days (from Mar.30/31 to April month end = 1 mo. + 27 days)

The days interval is > 0 if there is at least 24 hours between the two dates.

The interval from one day in standard time and another in daylight savings time is considered to be 24 hours. (All times are converted to standard time before calculating the difference.)

Not so easy, is it?

anil_mujagic

Yes kine you're right... it will not be so simple as I though ](*,)

Chowdary

thank you, it's helpful

wic

hello,
suddenly, DateEnd - DateStart is giving me 0. (31.12.2018-01.12.2018). Don't understand why?
*** FORGET IT- EMOTION -
had a variable with same name as field in a WITH ..DO BEGIN statement.