Date Difference - How to calculate
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 (**Use this code if the expression will be used for criteria selection)
(to_date (SYSDATE) – to_date(TEST_DT)) / 365 (**Use this code for display purposes)
Save the expression and ‘use as field’ for the value to appear in your report.
...
Expression Text:
(SYSDATE - TEST_DT) / 365
or
(to_date (SYSDATE) – to_date(TEST_DT)) / 365
Add Prompt Add Field
...