CODEWITHSUNDEEP
oracle-database
Krishna Chaitanya
Krishna Chaitanya

Reputation: 11

How to get End date of last 12 months in Oracle

I need a query where i can get last 12 month's end date from the present system date in oracle .

Below is the sample query i am using in Oracle

select
  trunc(add_months(sysdate,level-1),'MM') first_day,
  last_day(add_months(sysdate,level-1)) last_day
  from dual
connect by level<=12;

Upvotes: 1

Views: 3753

Answers (2)

valentin
valentin

Reputation: 3608

    select 
      trunc(add_months(sysdate-numtoyminterval(1, 'YEAR'),level-1),'MM')  first_day, 
      last_day(add_months(sysdate-numtoyminterval(1, 'YEAR'),level-1)) last_day 
from dual 
connect by level<=12;

Instead to start now i.e. sysdate, the start will be one year ago: sysdate-numtoyminterval(1, 'YEAR')

Upvotes: 1

UltraCommit
UltraCommit

Reputation: 2276

Try this please:

    SELECT   TRUNC (ADD_MONTHS (SYSDATE, -(LEVEL - 1)), 'MM') FIRST_DAY,
             LAST_DAY (ADD_MONTHS (SYSDATE, -(LEVEL - 1))) LAST_DAY
      FROM   DUAL
CONNECT BY   LEVEL <= 12;

Upvotes: 1

Related Questions