Home » SQL & PL/SQL » SQL & PL/SQL » Getting months in the current Fiscal Year in Oracle (Oracle Database 11g Enterprise Edition)
Getting months in the current Fiscal Year in Oracle [message #657304] Fri, 04 November 2016 14:59 Go to next message
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
Re: Getting months in the current Fiscal Year in Oracle [message #657305 is a reply to message #657304] Fri, 04 November 2016 15:29 Go to previous messageGo to next message
BlackSwan
Messages: 26208
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
Re: Getting months in the current Fiscal Year in Oracle [message #657306 is a reply to message #657304] Fri, 04 November 2016 16:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
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.

Re: Getting months in the current Fiscal Year in Oracle [message #657307 is a reply to message #657304] Fri, 04 November 2016 16:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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.
Re: Getting months in the current Fiscal Year in Oracle [message #657439 is a reply to message #657307] Tue, 08 November 2016 09:00 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Thank you everyone and sorry about the wrong example.

Megha
Re: Getting months in the current Fiscal Year in Oracle [message #657446 is a reply to message #657439] Wed, 09 November 2016 03:01 Go to previous message
Flyby
Messages: 187
Registered: March 2011
Location: Belgium
Senior Member
You could also create a fiscal year table to join with. calenderdate, fiscalyear
Previous Topic: Query help
Next Topic: Search data between months
Goto Forum:
  


Current Time: Tue Oct 23 13:56:02 CDT 2018