CODEWITHSUNDEEP
sqloracle-database
eraelpeha
eraelpeha

Reputation: 419

List of years between two dates

I have a table with columns for a start- and enddate. My goal is to get a list of each year in that timespan for each row, so

+-------------------------+
| startdate  | enddate    |
+------------+------------+
| 2004-08-01 | 2007-01-08 |
| 2005-06-02 | 2007-05-08 |
+------------+------------+

should output this:

+-------+
| years |
+-------+
| 2004  |
| 2005  |
| 2006  |
| 2007  |
| 2005  |
| 2006  |
| 2007  |
+-------+

I have problems now to create the years in between the two dates. My first approach was to use a UNION (order of dates is irrelevant), but the years in between are missing in this case...

Select
  Extract(Year From startdate)
From
  table1
Union
Select
  Extract(Year From enddate)
From
  table1

Thanks for any advises!

Upvotes: 0

Views: 5177

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

Row Generator technique

SQL> WITH DATA1 AS(
  2  SELECT TO_DATE('2004-08-01','YYYY-MM-DD') STARTDATE, TO_DATE('2007-01-08','YYYY-MM-DD') ENDDATE FROM DUAL UNION ALL
  3  SELECT TO_DATE('2005-06-02','YYYY-MM-DD') STARTDATE, TO_DATE('2007-05-08','YYYY-MM-DD') ENDDATE FROM DUAL
  4  ),
  5  DATA2 AS(
  6  SELECT EXTRACT(YEAR FROM STARTDATE) ST, EXTRACT(YEAR FROM ENDDATE) ED FROM DATA1
  7  ),
  8  data3
  9  AS
 10    (SELECT level-1 line
 11    FROM DUAL
 12      CONNECT BY level <=
 13      (SELECT MAX(ed-st) FROM data2
 14      )
 15    )
 16  SELECT ST+LINE FROM
 17  DATA2, DATA3
 18  WHERE LINE <= ED-ST
 19  ORDER BY 1
 20  /

   ST+LINE
----------
      2004
      2005
      2005
      2006
      2006
      2007

6 rows selected.

SQL>

Upvotes: 3

Joe Taras
Joe Taras

Reputation: 15379

Try this:

Create a table with years as follow:

CREATE TABLE tblyears(y int)

INSERT INTO tblyears VALUES (1900);
INSERT INTO tblyears VALUES (1901);
INSERT INTO tblyears VALUES (1902);

and so on until

INSERT INTO tblyears VALUES (2100)

So, you'll write this query:

SELECT y.y
FROM tblyears y
JOIN table1 t
ON y.y >= EXTRACT(year from startdate)
AND y.y <= EXTRACT(year from enddate)
ORDER BY y.y

Show SqlFiddle

Upvotes: 0

Iren Patel
Iren Patel

Reputation: 757

Try this Query

; with  CTE as
        (
        select  datepart(year, '2005-12-25') as yr
        union all
        select  yr + 1
        from    CTE
        where   yr < datepart(year, '2013-11-14')
        )
select  yr
from    CTE

Upvotes: 2

Related Questions