HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Best way to get current year minus 5 years

Submitted by: @import:stackexchange-dba··
0
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'.

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.

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

Code 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 specified
SQL> select to_char(trunc(add_months(to_Date('2016-02-29','YYYY-MM-DD'),-12*5),'YEAR'),'YYYYMM') 
     from dual;

TO_CHA
------
201101

Context

StackExchange Database Administrators Q#191197, answer score: 8

Revisions (0)

No revisions yet.