HDIFF: Finding the Number of Units Between Two Date-Time Values

How to:


The HDIFF function calculates the number of date or time component units between two date-time values.

Top of page

Reference: Usage Notes for HDIFF

HDIFF does its subtraction differently from DATEDIF, which subtracts date components stored in date fields. The DATEDIF calculation looks for full years or full months. Therefore, subtracting the following two dates and requesting the number of months or years, results in 0:

DATE1 12/25/2014, DATE2 1/5/2015   

Performing the same calculation using HDIFF on date-time fields results in a value of 1 month or 1 year as, in this case, the month or year is first extracted from each date-time value, and then the subtraction occurs.

Top of page

Syntax: How to Find the Number of Units Between Two Date-Time Values
HDIFF(end_dt, start_dt, 'component', output)




Is the date-time value to subtract from.



Is the date-time value to subtract.



Is the name of the component to be used in the calculation, enclosed in single quotation marks. If the component is a week, the WEEKFIRST parameter setting is used in the calculation.


Floating-point double-precision

Example: Finding the Number of Units Between Two Date-Time Values

Assume that we have a date-time field DTCUR in H format, which is has a current date and time timestamp. To find the number of days from President's Day 2004 to today use the expression:

DIFDAY/I6 = HDIF(DTCUR, DT(2004/02/16), 'DAY', 'D6.0')

The function returns the number of days in double precision floating point format, then assigns it to DIFDAY as integer value. If today is March 31, 2004, the DIFDAY is assigned to 46.

If you wish to obtain results in seconds, use the expression

DIFSEC/I9 = HDIF(DTCUR, DT(2004 February 16), 'SECOND', 'D9.0')

which assigns 3801600 to DIFSEC. Note that the format 'D9.0' is used with HDIF. Using 'I9' for an output_format in HDIF is invalid.

iWay Software