CODEWITHSUNDEEP
oracle-databasesysdate
piguy
piguy

Reputation: 526

Oracle sysdate flexible date

I worked this statement out

SELECT to_date('30.06.2016', 'dd.mm.yyyy') - (LEVEL-1) DATUM 
        FROM DUAL
        CONNECT BY LEVEL <= 366;

which gives me all dates from 30.06.2016 till 366 days in the past.

So far so good.

What I need to add is that to_date('30.06.2016') is more flexible..

What I mean I always want it to use the last day of June in sysdate + 1 year.

In this case we have 2015 at the moment - so we have 30.06.2016.

If we had 2016 I need it to use 30.06.2017. If we had 2017 I need it to use 30.06.2018. .. ..

Thanks for your help.

EDIT Solution:

SELECT last_day(add_months(to_date('01.06.' || to_char(sysdate, 'YYYY'), 'dd.mm.yyyy'),12)) - (LEVEL-1) DATUM 
        FROM DUAL
        CONNECT BY LEVEL <= 366

Upvotes: 0

Views: 239

Answers (3)

MT0
MT0

Reputation: 168051

If you want 366 days worth of dates:

SELECT TRUNC( SYSDATE, 'YEAR' ) + INTERVAL '18' MONTH - LEVEL AS DATUM
FROM   DUAL
CONNECT BY LEVEL <= 366;

Or if you want a year's worth (365 days or 366 days in a leap year) of dates (1st July this year to 30th June next year):

SELECT TRUNC( SYSDATE, 'YEAR' ) + INTERVAL '18' MONTH - LEVEL AS DATUM
FROM   DUAL
CONNECT BY TRUNC( SYSDATE, 'YEAR' ) + INTERVAL '18' MONTH - LEVEL >= TRUNC( SYSDATE, 'YEAR' ) + INTERVAL '6' MONTH;

Upvotes: 1

Florin Ghita
Florin Ghita

Reputation: 17643

Is your same code, but get from sysdate the year, using to_char:

select to_date('30.06.'||(to_char(sysdate,'yyyy')+1),'dd.mm.yyyy') from dual;

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52376

Here's the steps:

  1. Truncate sysdate to the year, using Trunc().
  2. Add 18 months, using Add_Months().
  3. Subtract one day.

Upvotes: 0

Related Questions