Dynamic list for Years , Months & Days

While developing applications I need often list-of-values, which have a result-set of years, months or days.

You can hard-code those selects each time in each form, but much more elegant is to create views, which do the whole work. 

Here are 3 views for years, months and days:

CREATE OR REPLACE FORCE VIEW YEARS_V 
  (YDATE) AS
SELECT add_months (trunc (sysdate, 'YYYY'), 12 * (50 - Level))
  FROM Dual
CONNECT BY Level <= 100;

CREATE OR REPLACE FORCE VIEW MONTHS_V 
  (MDATE) AS 
SELECT add_months (trunc (sysdate, 'MM'), 500 - Level)
  FROM Dual
CONNECT BY Level <= 1000;

CREATE OR REPLACE FORCE VIEW DAYS_V 
  (DDATE) AS 
SELECT trunc (sysdate) + 15000 - Level
  FROM Dual
CONNECT BY Level <= 30000;

Those Views helps us to Select Data for the

- actual year  +/- 50 years
- actual month +/- 500 months
- actual day   +/- 15000 days

In a Record-Group you can use those views:

LOV of the next 10 years

SELECT YDATE
  FROM Years_V
 WHERE YDATE BETWEEN trunc (sysdate, 'YYYY') 
             AND add_months (trunc (sysdate, 'YYYY'), 10*12);

LOV of the last 30 and the next 10 days

SELECT DDATE
  FROM Days_V
 WHERE DDATE BETWEEN trunc (sysdate-30) AND trunc (sysdate+10);