subtracting two years with to_char and to

I need to subtract two dates. First I convert the date field to years using to_char, then convert to a number using to_number. However I get an error in the query when subtracting two years:

to_char((order date),'yyyy')
to_char((beginning of the year),'yyyy')

I tried converting to a number using

to_number(to_char((order date),'yyyy'),'9999')
to_number(to_char((beginning of the year),'yyyy'),'9999')

CodePudding user response：

Use extract; it is simpler than what you're doing:

select extract(year from order_date) - extract(year from trunc(sysdate, 'yyyy')) as result
from some_table

CodePudding user response：

You can use the EXTRACT function:

EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM order_date)

Or, if you want to get the actual difference and measure full years (accounting for months and not just years), then you can use MONTHS_BETWEEN and divide by 12 and round down:

FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE, 'YYYY'), order_date) / 12)