I want to display result set of Years between From date - To date using oracle SQL on dual table
e.g.
if i pass - From date as 1/1/1900 and To Date as 1/1/2000
then it shoold display
Only Years
1900
1901
1902
-
-
2000
Add a comment
|
1 Answer
There are two parts to this question. Generating the range of dates is quite simple: just use the trick with CONNECT BY that I demonstrated here.
edit
Generating a list of first of New Year's Days is quite simple:
SQL> select add_months(to_date('01-jan-1900'), (level-1)*12) as year
2 from dual
3 connect by level <= 101
4 /
YEAR
---------
01-JAN-00
01-JAN-01
01-JAN-02
...
01-JAN-98
01-JAN-99
01-JAN-00
101 rows selected.
SQL>
You just want the years? Well either use to_char(... , 'YYYY') on that. Or cut to the chase and just generate a list of numbers from 1900 - 2000.
The trickiest part of your request is getting the number of years. It would be easier to be given a start date and an offset, rather than an end date. Anyway ...
SQL> select to_char(add_months(to_date('&&start_date'), (level-1)*12), 'YYYY') as year
2 from dual
3 connect by level <= ( to_number(to_char(to_date('&&end_date'), 'yyyy'))
4 -to_number(to_char(to_date('&&start_date'), 'yyyy')) ) + 1
5 /
Enter value for start_date: 01-jan-1900
old 1: select add_months(to_date('&&start_date'), (level-1)*12) as year
new 1: select add_months(to_date('01-jan-1900'), (level-1)*12) as year
Enter value for end_date: 01-jan-2000
old 3: connect by level <= ( to_number(to_char(to_date('&&end_date'), 'yyyy'))
new 3: connect by level <= ( to_number(to_char(to_date('01-jan-2000'), 'yyyy'))
old 4: -to_number(to_char(to_date('&&start_date'), 'yyyy')) ) - 1
new 4: -to_number(to_char(to_date('01-jan-1900'), 'yyyy')) ) - 1
YEAR
----
1900
1901
1902
...
1998
1999
2000
101 rows selected.
SQL>
