OraFAQ Forum: SQL & PL/SQL » Getting months in the current Fiscal Year in Oracle

Home » SQL & PL/SQL » SQL & PL/SQL » Getting months in the current Fiscal Year in Oracle (Oracle Database 11g Enterprise Edition)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Getting months in the current Fiscal Year in Oracle [message #657304]

Fri, 04 November 2016 14:59

megha2525
Messages: 62
Registered: June 2012
Location: columbus

Member

Where I work the fiscal year is between July 1 and June 30. Based on user provided parameter (period_name), I need to display the months in that fiscal year.

EXAMPLE1 : period_name is SEP-16, then display JUL-16, AUG-16, SEP-16.
EXAMPLE2 : period_name is  MAR-15, then display 
JUL-15,
AUG-15,
SEP-15,
OCT-15,
NOV-15,
DEC-15,
JAN-15,
FEB-15,
MAR-15

How can I accomplish this using Oracle SQL or PL/SQL code.

Thanks,
Megha

Report message to a moderator

Re: Getting months in the current Fiscal Year in Oracle [message #657305 is a reply to message #657304]

Fri, 04 November 2016 15:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Getting months in the current Fiscal Year in Oracle [message #657306 is a reply to message #657304]

Fri, 04 November 2016 16:03

Michel Cadot
Messages: 68636
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Your sentences and examples are inconsistent.
Please carefully read what you post before clicking on "Create topic" button.

Report message to a moderator

Re: Getting months in the current Fiscal Year in Oracle [message #657307 is a reply to message #657304]

Fri, 04 November 2016 16:55

Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA

Senior Member

Well, for MAR-15 output should be:

JUL-14,
AUG-14,
SEP-14,
OCT-14,
NOV-14,
DEC-14,
JAN-15,
FEB-15,
MAR-15

Anyway:

SQL> select  to_char(
  2                  trunc(
  3                        to_date('&user_input','MON-YY') - numtoyminterval(6,'month'),
  4                        'yy'
  5                       ) + numtoyminterval(level + 5,'month'),
  6                  'MON-YY'
  7                 ) dt
  8    from  dual
  9    connect by level <= 12 - to_char(to_date('&user_input','MON-YY'),'MM')
 10  /
Enter value for user_input: SEP-16
old   3:                       to_date('&user_input','MON-YY') - numtoyminterval(6,'month'),
new   3:                       to_date('SEP-16','MON-YY') - numtoyminterval(6,'month'),
Enter value for user_input: SEP-16
old   9:   connect by level <= 12 - to_char(to_date('&user_input','MON-YY'),'MM')
new   9:   connect by level <= 12 - to_char(to_date('SEP-16','MON-YY'),'MM')

DT
------
JUL-16
AUG-16
SEP-16

SQL> /
Enter value for user_input: MAR-15
old   3:                       to_date('&user_input','MON-YY') - numtoyminterval(6,'month'),
new   3:                       to_date('MAR-15','MON-YY') - numtoyminterval(6,'month'),
Enter value for user_input: MAR-15
old   9:   connect by level <= 12 - to_char(to_date('&user_input','MON-YY'),'MM')
new   9:   connect by level <= 12 - to_char(to_date('MAR-15','MON-YY'),'MM')

DT
------
JUL-14
AUG-14
SEP-14
OCT-14
NOV-14
DEC-14
JAN-15
FEB-15
MAR-15

9 rows selected.

SQL>

SY.

Report message to a moderator