DTDIFF: Returning the Number of Component Boundaries Between Date or Date-Time Values

How to:

Given two dates in standard date or date-time formats, DTIFF returns the number of given component boundaries between the two dates. The returned value has integer format for calendar components or double precision floating point format for time components.

Syntax: How to Return the Number of Component Boundaries 

DTDIFF(end_date, start_date, component)

where:

end_date

Date or date-time

Is the ending full-component date in either standard date or date-time format. If this date is given in standard date format, all time components are assumed to be zero.

start_date

Date or date-time

Is the starting full-component date in either standard date or date-time format. If this date is given in standard date format, all time components are assumed to be zero.

component

Keyword

Is the component on which the number of boundaries is to be calculated. For example, QUARTER finds the difference in quarters between two dates. Valid components (and acceptable values) are:

  • YEAR (1-9999).
  • QUARTER (1-4).
  • MONTH (1-12).
  • WEEK (1-53). This is affected by the WEEKFIRST setting.
  • DAY (of the Month, 1-31).
  • HOUR (0-23).
  • MINUTE (0-59).
  • SECOND (0-59).

Example: Returning the Number of Years Between Two Dates

The following request against the WF_RETAIL data source calculates employee age when hired: 

DEFINE FILE WFLITE
YEARS/I9 = DTDIFF(START_DATE, DATE_OF_BIRTH, YEAR); 
END
TABLE FILE WFLITE
PRINT START_DATE DATE_OF_BIRTH YEARS AS 'Hire,Age'
BY  EMPLOYEE_NUMBER 
WHERE EMPLOYEE_NUMBER CONTAINS 'AA'
ON TABLE SET PAGE NOPAGE
END

The output is: