Generate a range of dates using Sql?

Question

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.

Does anyone know of an easy way 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 1

This should get you started. You will need to check that the boundary conditions are correct for your scenario (ie. did you want today included - this starts at yesterday).

WITH DAYS AS
(SELECT TRUNC(SYSDATE) - ROWNUM D
 FROM ALL_OBJECTS
 WHERE ROWNUM < 365)
SELECT
  DAYS.D
FROM
  DAYS;

To break this down a little. This part defines a table-like thing you can use in the following part of the SQL:

WITH DAYS AS
(SELECT TRUNC(SYSDATE) - ROWNUM D
 FROM ALL_OBJECTS
 WHERE ROWNUM < 365)

Then, you can act like "DAYS" is a table in the SELECT part of the statement.

Answer 2

Ahahaha, here's a funny way I just came up with to do this:

select SYSDATE - ROWNUM
from shipment_weights sw
where ROWNUM < 365;

where shipment_weights is any large table;

Answer 3

For the fun of it, here's some code that should work in SQL Server, Oracle, or MySQL:

SELECT current_timestamp - CAST(d1.digit + d2.digit + d3.digit as int)
FROM 
(
 SELECT digit
 FROM
 (
  select '1' as digit
  union select '2'
  union select '3'
  union select '4'
  union select '5'
  union select '6'
  union select '7'
  union select '8'
  union select '9'
  union select '0'
 ) digits
) d1
CROSS JOIN
(
 SELECT digit
 FROM
 (
  select '1' as digit
  union select '2'
  union select '3'
  union select '4'
  union select '5'
  union select '6'
  union select '7'
  union select '8'
  union select '9'
  union select '0'
 ) digits
) d2
CROSS JOIN
(
 SELECT digit
 FROM
 (
  select '1' as digit
  union select '2'
  union select '3'
  union select '4'
  union select '5'
  union select '6'
  union select '7'
  union select '8'
  union select '9'
  union select '0'
 ) digits
) d3
WHERE CAST(d1.digit + d2.digit + d3.digit as int) < 365
ORDER BY d1.digit, d2.digit, d3.digit -- order not really needed here

Bonus points if you can give me a cross-platform syntax to re-use the digits table.

Answer 4

I don't have the answer to re-use the digits table but here is a code sample that will work at least in SQL server and is a bit faster.

print("code sample");

select  top 366 current_timestamp - row_number() over( order by l.A * r.A) as DateValue
from (
select 1 as A union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10 union
select 11 union
select 12 union
select 13 union
select 14 union
select 15 union
select 16 union
select 17 union
select 18 union
select 19 union
select 20 union
select 21 
) l
cross join (
select 1 as A union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10 union
select 11 union
select 12 union
select 13 union
select 14 union
select 15 union
select 16 union
select 17 union
select 18
) r
print("code sample");

Answer 5

Oracle specific, and doesn't rely on pre-existing large tables or complicated system views over data dictionary objects.

SELECT c1 from dual
  MODEL DIMENSION BY (1 as rn)  MEASURES (sysdate as c1)
  RULES ITERATE (365) 
  (c1[ITERATION_NUMBER]=SYSDATE-ITERATION_NUMBER)
order by 1

Answer 6

A method quite frequently used in Oracle is something like this:

select trunc(sysdate)-rn
from
(   select rownum rn
    from   dual
    connect by level <= 365)
/

Personally, if an application has a need for a list of dates then I'd just create a table with them, or create a table with a series of integers up to something ridiculous like one million that can be used for this sort of thing.

Answer 7

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.

Answer 8

About a year and a half too late, but for posterity here is a version for Teradata:

SELECT calendar_date 
FROM SYS_CALENDAR.Calendar
WHERE SYS_CALENDAR.Calendar.calendar_date between '2010-01-01' (date) and '2010-01-03' (date)