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. 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.
...
Number Length: 2
...
Expression Text:
...
or
(to_date (SYSDATE) – to_date(TEST_DT)) / 365
...
The following images illustrated calculation of date using Expression:
OK Cancel
