Calculating the difference in Query is done by creating an expression. 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. This will produce the difference between the two dates in years, 365 being the number of days in a year.
(SYSDATE – TEST_DT) / 365
Save the expression and ‘use as field’ for the value to appear in your report.
Edit Expression Properties
*Expression Type:
Number Length: 2
Aggregate Function Decimals: 0
Expression Text:
(SYSDATE - TEST_DT) / 365
Add Prompt Add Field
OK Cancel