Calculate difference between two dates?

anil_mujagicanil_mujagic Member Posts: 91
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

Comments

  • SteveOSteveO Member Posts: 164
    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.
    This isn't a signature, I type this at the bottom of every message
  • DenSterDenSter Member Posts: 8,304
    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_mujagicanil_mujagic Member Posts: 91
    edited 2007-04-04
    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 :wink:

    TNX!
  • anil_mujagicanil_mujagic Member Posts: 91
    Or... if I'm on the wrong path feel free to show me the right path.
  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • maschmasch Member Posts: 8
    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.
  • DenSterDenSter Member Posts: 8,304
    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_mujagicanil_mujagic Member Posts: 91
    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. :lol:
  • kinekine Member Posts: 12,562
    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?
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • anil_mujagicanil_mujagic Member Posts: 91
    Yes kine you're right... it will not be so simple as I though ](*,)
  • ChowdaryChowdary Member Posts: 148
    thank you, it's helpful
    Pleasure in the job puts perfection in the work
  • wicwic Member Posts: 96
    edited 2018-09-20
    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.
    #### Only one can survive ######
Sign In or Register to comment.