DATES/Years/Quarters

Hi, I need to have two List of Values:
One that shows this year and next year:
2011 d, 01/01/2011 r
2012 d, 01/01/2012 r

and one
Quarter One d, 01/01/2010 r
Quarter Two d, 04/01/2010 r
Quarter Three d, 07/01/2010 r
Quarter Four d, 10/01/2010 r

BUT they need to changed based on the changing of time, 2013,2014,etc
Open to better ideas also...

Thanks, Bill
LVL 2
BILL CarlisleAPEX DeveloperAsked:
Who is Participating?
 
awking00Commented:
For list1 -
select add_months(trunc(sysdate,'yyyy'),(level - 1) * 12)
from dual
connect by level <= 2;

For list2 -
select add_months(trunc(sysdate,'yyyy'),(level - 1) * 3)
from dual
connect by level <= 4;
0
 
DavidSenior Oracle Database AdministratorCommented:
One idea is to parse the sysdate year and increment it for each item in your LOV, e.g., TO_DATE(sysdate,'RR') +1
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
this only gives one row?? TO_DATE(sysdate,'RR') +1

For the two years this is what I have so far:
select to_char(add_months(sysdate,rownum*12 -12),'yyyy') d,
           trunc(add_months(sysdate, rownum*12 -12),'year') r
from my_table --- must have two + rows
where rownum <3
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BILL CarlisleAPEX DeveloperAuthor Commented:
For Quarter:
select 'Q'||rownum d, trunc(add_months(sysdate, rownum*3),'q') r
from om_org
where rownum <5
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
select 'Q'||rownum d, trunc(add_months(to_date(:P1051_YEAR), rownum*3 -3),'q') r
from om_org
where rownum <5
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
These feed an IR report
Only one YEAR can be selected but one or more quarters can be selected.

So now I need the best was to add these to the WHERE statement
WHERE  ???????
0
 
awking00Commented:
For clarity, add alias -
select add_months(trunc(sysdate,'yyyy'),(level - 1) * 12) as year
from dual
connect by level <= 2;

select add_months(trunc(sysdate,'yyyy'),(level - 1) * 3) as quarter
from dual
connect by level <= 4;
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
Thank you! Sorry for delay of response... I used the connect by and forgot to thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.