3

I'm actually have the next query that I use to obtain the current year minus 5 years in january in this format 'YYYYMM'.

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.

2 Answers 2

8

There are different ways to achieve the goal.

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 specified

What 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
------
201101
2

SQL>

select trunc(sysdate)AS TODAY,
2  ADD_MONTHS(TRUNC(SYSDATE), 12 * 5 * -1) AS minus_5_year from dual;

Result:

TODAY       MINUS_5_YEAR
----------- ------------
04/03/2021  04/03/2016

for leap day

SQL>

select to_date('29/02/2020')AS TODAY,
  2  ADD_MONTHS(to_date('29/02/2020'), 12 * 5 * -1) AS minus_5_year from dual;

Result:

TODAY       MINUS_5_YEAR
----------- ------------
29/02/2020  28/02/2015 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.