Date Difference in SQL

If you want to calculate the difference (duration) bewteen two dates or time values in years, months or days, or hours RPG provides the OpCode SUBDUR; or in free format, the Built-in-Function %DIFF.


Fixed Format

D due_date S D INZ(D'2005-06-01')
D today S D INZ(D'2004-09-23')
D num_days S 15P 0
D start_time S Z
D time_taken S 15P 0

/FREE
// Determine the number of days between two dates.
// If due_date has the value 2005-06-01 and
// today has the value 2004-09-23, then
// num_days will have the value 251.

   num_days = %DIFF (due_date: today: *DAYS);

But what if you want to calculate the difference in years and months and days between two dates? SQL will perform the function, subtracting two date fields from each other, with resulting numeric value containing the year, month and day--as demonstrated below.


D                 DS
D DiffDate                       8  0
D   DiffYears                    4  0 overlay(DiffDate)
D   DiffMonths                   2  0 overlay(DiffDate: *Next)
D   DiffDays                     2  0 overlay(DiffDate: *Next)

D Date1           S               D   inz(D'2004-07-01')
D Date2           S               D   inz(D'2003-01-31')

c/EXEC SQL
C+    set :DiffDate  = :Date1 - :Date2
c/END-EXEC

Position 1-4 = years
Position 5-6 = months
Position 7-8 = days
DiffDate = 10501 --> 1 year, 5 months and 1 day