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.


Top of page

x
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 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 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

DTDIFF calculates employee age when hired:

DTDIFF(START_DATE, DATE_OF_BIRTH, YEAR)

For the date of birth 1991/06/04 and the start date 2008/11/14, the result is 17.


iWay Software