Date and time math is simple with %DIFF.

Written by Joe Pluta

This TechTip introduces you to the %DIFF BIF, a powerful function that allows you to find the difference between any two compatible date or time variables (including timestamps). This is just one more reason to start using real time and date variables in your programs and your database definitions!

The Three Data Types

The three data types that are supported by %DIFF are the date, time, and timestamp types. You specify these types using the data type (position 40) of the D spec. Type D indicates a date, type T a time, and type Z a timestamp. You identify these field types in your database using position 35 of the DDS specification. With the exception of dates, you specify the same type in the DDS as in your RPG program. Although you use type D for a date in a D-spec, for reasons lost in the murk of time you use type L in the database. In DDL, you declare the variables as type DATE, TIME, and TIMESTAMP.

Now that you have the three data types, you can use them. A quick example:

numDays = %diff( date2: date1: *days);

This snippet takes two dates, date1 and date2, and calculates the difference between the two in days. Note the order of the operands; the second date is subtracted from the first. This happens for all variations of the BIF.

When you're calculating the difference between two dates, you can get the difference in days, months, or years. If you use a unit greater than days, the difference is rounded down.

Let's take another example:

indToConst = %diff( D'1788-06-21': D'1776-07-04': *years);

The variable indToConst is set to 11. Because the answer is rounded down, the difference between July 4, 1776 (D'1776-07-04') and June 21, 1788 (D'1788-06-21') is calculated as 11 years. You in fact get that for any date from July 4, 1787 to July 3, 1788. This rounding also affects any other variations of DIFF.

What else can you calculate? You can calculate the difference between any two variables of the same type. As already noted, you can calculate the difference between two dates in years, months, or days. You can calculate the difference between two times in hours, minutes, or seconds. And you can calculate the difference between two timestamps in any supported unit: years, months, days, hours, minutes, seconds, and milliseconds. The unit is always specified in the third parameter to the BIF:

• Years: *YEARS or *Y
• Months: *MONTHS or *M
• Days: *DAYS or *D
• Hours: *HOURS or *H
• Minutes: *MINUTES or *MN
• Seconds: *SECONDS or *S
• Milliseconds: *MSECONDS or*MS

Additionally, you can calculate the difference between a date and a timestamp or between a time and a timestamp. When you do this, the system considers only the appropriate part of the timestamp: when comparing with a date, only the date part of the timestamp is used, and when comparing with a time, only the time part is used. You can then add the result directly to another variable.

Let's do an exercise in prediction. Suppose we had two events occur some minutes apart. How do we predict when the next one will occur? Here's a little snippet that will do that:

nextTime = lastTime + %diff( lastTime2: lastTime: *minutes);

The %DIFF calculates the difference between lastTime and lastTime2 in minutes, adds the value to lastTime, and places the result in nextTime. If lastTime was T'12:30:00' and lastTime2 was T'11:45:00', the result would be T'13:15:00'. That's a pretty slick way to do time calculations!

Final note: you can crash the API. Since the maximum size of the returned value is 15 digits, if you try to take the difference in milliseconds between two timestamps more than 32 years or so apart, you'll get an overflow error.

Other than that, though, you can take advantage of %DIFF to make your date handling a lot easier. Have fun!