DATEDIF: Finding the Difference Between Two Dates

Available Languages: reporting, Maintain

The DATEDIF function returns the difference between two full component standard dates in units of a specified component. A component is one of the following:

  • Year. Using the year unit with DATEDIF yields the inverse of DATEADD. If subtracting one year from date X creates date Y, then the count of years between X and Y is one. Subtracting one year from February 29 produces the date February 28.
  • Month. Using the month component with DATEDIF yields the inverse of DATEADD. If subtracting one month from date X creates date Y, then the count of months between X and Y is one. If the to-date is the end-of-month, then the month difference may be rounded up (in absolute terms) to guarantee the inverse rule.

    If one or both of the input dates are the end of the month, DATEDIF takes this into account. This means that the difference between January 31 and April 30 is three months, not two months.

  • Day.
  • Weekday. With the weekday unit, DATEDIF does not count Saturday or Sunday when calculating days. This means that the difference between Friday and Monday is one day.
  • Business day. With the business day unit, DATEDIF uses the BUSDAYS parameter setting and holiday file to determine which days are working days and disregards the rest. This means that if Monday is not a working day, the difference between Friday and Tuesday is one day. See Using Standard Date Functions for more information.

DATEDIF returns a whole number. If the difference between two dates is not a whole number, DATEDIF truncates the value to the next largest integer. For example, the number of years between March 2, 2001, and March 1, 2002, is zero. If the end date is before the start date, DATEDIF returns a negative number.

You can find the difference between non-day based dates (for example YM or YQ) directly without using DATEDIF.

Since Dialogue Manager interprets a date as alphanumeric or numeric, and DATEDIF requires a standard date stored as an offset from the base date, do not use DATEDIF with Dialogue Manager unless you first convert the variable used as the input date to an offset from the base date.

For more information, see Calling a Function From a Dialogue Manager Command.

DATEDIF works only with full component dates.

Find the Difference Between Two Dates

DATEDIF(from_date, to_date, 'component')
DATEDIF('from_date', 'to_date', 'component')

where:

from_date

Date

Is the start date from which to calculate the difference. Is a full component date.

to_date

Date

Is the end date from which to calculate the difference.

component

Alphanumeric

Is one of the following enclosed in single quotation marks:

Y indicates a year unit.

M indicates a month unit.

D indicates a day unit.

WD indicates a weekday unit.

BD indicates a business day unit.

Note: DATEDIF does not use an output argument because for the result it uses the format 'I8'.

Finding the Difference Between Two Dates

The example finds the number of complete months between today, March 23, 2004, and one specific day in the past

DATEDIF('September 11 2001', '20040323', 'M')

and returns 30, which can be assigned to a numeric field.

Tip: There is an alternative way to find the difference between dates. As long as any standard date is presented internally as a whole number of the least significant component units (that is, a number of days for full component dates, a number of months for YYM or MY format dates, etc.), you can find the difference in these component units (not any units) directly, without DATEDIF. For example, assume OLD_YYM_DT is a date field in format MYY and NEW_YYM_DT is another date in format YYM. Note that the least significant component for both formats is month, M. The difference in months, then, can be found by subtracting the field OLD_YYM_DT from NEW_YYM_DT in the following statement:
MYDIFF/I8 = NEW_YYM_DT/YYM - OLD_YYM_DT;

Otherwise, non-full component standard dates or legacy dates should be converted to full component standard dates before using DATEDIF.

Truncation With DATEDIF

DATEDIF calculates the difference between March 2, 1996, and March 1, 1997, and returns a zero because the difference is less than a year:

DATEDIF('19960302', '19970301', 'Y')

Using Month Calculations

The following expressions return a result of minus one month:

DATEDIF('19990228', '19990128', 'M')
DATEDIF('19990228', '19990129', 'M')
DATEDIF('19990228', '19990130', 'M')
DATEDIF('19990228', '19990131', 'M')

Additional examples:

DATEDIF( 'March 31 2001', 'May 31 2001', 'M') yields 2.

DATEDIF( 'March 31 2001', 'May 30 2001', 'M') yields 1 (because May 30 is not the end of the month).

DATEDIF( 'March 31 2001', 'April 30 2001', 'M') yields 1.

Finding the Number of Weekdays Between Two Dates (Reporting)

DATECVT converts the legacy dates in HIRE_DATE and DAT_INC to the date format YYMD. DATEDIF then uses those date formats to determine the number of weekdays between NEW_HIRE_DATE and NEW_DAT_INC:

TABLE FILE EMPLOYEE
PRINT FIRST_NAME AND
COMPUTE NEW_HIRE_DATE/YYMD = DATECVT(HIRE_DATE, 'I6YMD', 'YYMD'); AND
COMPUTE NEW_DAT_INC/YYMD = DATECVT(DAT_INC, 'I6YMD', 'YYMD'); AND
COMPUTE WDAYS_HIRED/I8 = DATEDIF(NEW_HIRE_DATE, NEW_DAT_INC, 'WD');
BY LAST_NAME
IF WDAYS_HIRED NE 0
WHERE DEPARTMENT EQ 'PRODUCTION';
END

The output is:

LAST_NAME     FIRST_NAME  NEW_HIRE_DATE  NEW_DAT_INC  WDAYS_HIRED
---------     ----------  -------------  -----------  -----------
IRVING        JOAN        1982/01/04     1982/05/14   94
MCKNIGHT      ROGER       1982/02/02     1982/05/14   73
SMITH         RICHARD     1982/01/04     1982/05/14   94
STEVENS       ALFRED      1980/06/02     1982/01/01   414
              ALFRED      1980/06/02     1981/01/01   153

Finding the Number of Years Between Two Dates (Maintain)

DATEDIF determines the number of years between DATE2 and DATE1:

MAINTAIN 
Case Top
compute DATE1/yymd = '20020717';
compute DATE2/yymd = '19880705';
compute DIFF/I3= DATEDIF(DATE2, DATE1, 'Y', DIFF);
type "<<DATE1  -  <<DATE2  = <DIFF  YEARS"
ENDCASE
END

The result is:

2002/07/17 -  1988/07/05 = 14 YEARS