Definition:
Query Manager reporting tools performs calculation (ADD, SUM, COUNT) using 'Expressions' that does not provided by default. In Query Manager, expressions can be used in two ways:
- As comparison values in selection criteria.
- As columns in the query output.
Date Difference
What Expression Does:
Calculating the date difference in Query is done by creating an expression. This will calculate the difference between the two dates in years, 365 being the number of days in a year.
Expression:
(SYSDATE – TEST_DT) / 365
How to calculate:
Click on the ‘Expressions’ tab within Query Manager to see the page below: the expression type is number; the length of the field is 2 for a ‘year’ calculation; in the Expression Text enter parenthesis, today’s date (SYSDATE), minus (-), the date field (i.e., date of birth), close parenthesis, slash (/) and the number 365.
Save the expression and ‘use as field’ for the value to appear in your report.
The following images illustrated calculation of date using Expression: