patternMinor
Best way to get current year minus 5 years
Viewed 0 times
minusyearwaygetcurrentyearsbest
Problem
I'm actually have the next query that I use to obtain the current year minus 5 years in january in this format 'YYYYMM'.
I want to know if this way is the best way to do this.
select TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12*5*-1),'YYYYMM') from dual;I want to know if this way is the best way to do this.
Solution
There are different ways to achieve the goal.
OR
Regarding the second one, what happens if the SYSDATE or the current date supplied happens to be a leap day?
What happens if we use the first method?
SQL> select to_char(trunc(add_months(sysdate,-12*5),'YEAR'),'YYYYMM') from dual;OR
SQL> select to_char(trunc(SYSDATE - interval '5' year,'YEAR'),'YYYYMM') from dual;Regarding the second one, what happens if the SYSDATE or the current date supplied happens to be a leap day?
SQL> select to_char(trunc(to_Date('2016-02-29','YYYY-MM-DD') - interval '5' year,'YEAR'),'YYYYMM')
from dual;
select to_char(trunc(to_Date('2016-02-29','YYYY-MM-DD') - interval '5' year,'YEAR'),'YYYYMM')
from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specifiedWhat happens if we use the first method?
SQL> select to_char(trunc(add_months(to_Date('2016-02-29','YYYY-MM-DD'),-12*5),'YEAR'),'YYYYMM')
from dual;
TO_CHA
------
201101Code Snippets
SQL> select to_char(trunc(add_months(sysdate,-12*5),'YEAR'),'YYYYMM') from dual;SQL> select to_char(trunc(SYSDATE - interval '5' year,'YEAR'),'YYYYMM') from dual;SQL> select to_char(trunc(to_Date('2016-02-29','YYYY-MM-DD') - interval '5' year,'YEAR'),'YYYYMM')
from dual;
select to_char(trunc(to_Date('2016-02-29','YYYY-MM-DD') - interval '5' year,'YEAR'),'YYYYMM')
from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specifiedSQL> select to_char(trunc(add_months(to_Date('2016-02-29','YYYY-MM-DD'),-12*5),'YEAR'),'YYYYMM')
from dual;
TO_CHA
------
201101Context
StackExchange Database Administrators Q#191197, answer score: 8
Revisions (0)
No revisions yet.