%DIFF (Difference Between Two Date, Time, or Timestamp Values)
%DIFF(op1 : op2 : unit {: frac })
The unit can be *MSECONDS, *SECONDS, *MINUTES, *HOURS, *DAYS, *MONTHS, or *YEARS. You can also use the following abbreviated forms of the unit: *MS, *S, *MN, *H, *D, *M, or *Y.
- Date and date
- Time and time
- Timestamp and timestamp
- Date and timestamp (only the date portion of the timestamp is considered)
- Time and timestamp (only the time portion of the timestamp is considered).
- For two dates or a date and a timestamp: *DAYS, *MONTHS, and *YEARS
- For two times or a time and a timestamp: *SECONDS, *MINUTES, and *HOURS
- For two timestamps: *MSECONDS, *SECONDS, *MINUTES, *HOURS, *DAYS, *MONTHS, and *YEARS
The results for *MONTHS and *YEARS may be surprising. See Unexpected Results.
If the third operand is *SECONDS or *S, and both of the operands are timestamps, you can specify a fourth parameter indicating the number of fractional seconds to return. You can specify a value between 0 and 12. This represents the number of decimal positions in the returned number of seconds.
The difference is calculated by subtracting the second operand from the first.
The result is rounded down, with any remainder discarded. For example, 61 minutes is equal to 1 hour, and 59 minutes is equal to 0 hours.
The value returned by the function is compatible with both type numeric and type duration. You can add the result to a number (type numeric) or a date, time, or timestamp (type duration).
If you ask for the difference in microseconds between two timestamps that are more than 32 years 9 months apart, you will exceed the 15-digit limit for duration values. This will result in an error or truncation. However, you can obtain the number of microseconds between any two dates by asking for the difference in seconds with 6 fractional seconds, and then multiply the resulting value by 1000000. For example, if the difference in seconds is 1041379205.123456, then the difference in microseconds is 1041379205123456.
For more information, see Date Operations or Built-in Functions.