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 |
|
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 #657307 is a reply to message #657304] |
Fri, 04 November 2016 16:55 |
Solomon Yakobson
Messages: 3275 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.
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:30:12 CDT 2024
|