Using QMF

Subtracting dates

The result of date subtraction is a duration that is expressed in years, months, and days between the two dates. A negative result is possible with date subtraction.

Suppose that you want to know how many years, months, and days project 1407 will take to complete. If you run the following SQL statement:

SELECT ENDD - STARTD
  FROM Q.PROJECT
  WHERE PROJNO = '1407'

QMF produces this report:

+--------------------------------------------------------------------------------+
|          COL1                                                                  |
|   -----------                                                                  |
|         20603                                                                  |
+--------------------------------------------------------------------------------+

The result is a numeric representation of the duration: 2 years, 6 months, and 3 days. You can treat this result like any other numeric value. The duration format for dates is yyyymmdd, where yyyy represents years, mm represents months, and dd represents days. Leading zeros are always truncated in the results.

To find out the number of weeks project 1405 would take to complete, run the following SQL statement:

SELECT (DAYS(ENDD) - DAYS(STARTD))/7
  FROM Q.PROJECT
  WHERE PROJNO = '1405'

QMF produces this report:

+--------------------------------------------------------------------------------+
|          COL1                                                                  |
|   -----------                                                                  |
|           130                                                                  |
+--------------------------------------------------------------------------------+

The result is a duration of 130 weeks.

Suppose that you would like to know, in terms of years, how many years it takes to complete project 1403. If you run this SQL statement:

SELECT (DAYS(ENDD)-DAYS(STARTD))/365.24
  FROM Q.PROJECT
  WHERE PROJNO='1403'

QMF produces this report:

+--------------------------------------------------------------------------------+
|                 COL1                                                           |
|   ------------------                                                           |
|                 3.31                                                           |
+--------------------------------------------------------------------------------+

Making durations easier to read

Suppose that you run the following SQL statement:

SELECT ENDD-STARTD
  FROM Q.PROJECT
  WHERE PROJNO='1403'

QMF produces this report:

+--------------------------------------------------------------------------------+
|          COL1                                                                  |
|   -----------                                                                  |
|         30327                                                                  |
+--------------------------------------------------------------------------------+

The result of this date subtraction is a duration of 3 years, 3 months, and 27 days.

To get results that are easier to read in a report, run the following SQL statement:

SELECT YEAR(ENDD - STARTD), MONTH(ENDD - STARTD), DAY(ENDD - STARTD)
  FROM Q.PROJECT
  WHERE PROJNO='1403'

QMF produces a report like this:

+--------------------------------------------------------------------------------+
|         YEARS       MONTHS         DAYS                                        |
|   -----------  -----------  -----------                                        |
|             3            3           27                                        |
+--------------------------------------------------------------------------------+

QMF changes the column headings on the QMF form to make the report more meaningful.