George Mauer George Mauer - 2 months ago 7
SQL Question

Generate a range of dates using SQL

I have a SQL query that takes a date parameter (if I were to throw it into a function) and I need to run it on every day of the last year.

How to generate a list of the last 365 days, so I can use straight-up SQL to do this?

Obviously generating a list 0..364 would work, too, since I could always:

SELECT SYSDATE - val FROM (...);

Answer

There's no need to use extra large tables or ALL_OBJECTS table:

SELECT TRUNC (SYSDATE - ROWNUM) dt
  FROM DUAL CONNECT BY ROWNUM < 366

will do the trick.