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.

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 1

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 2

 SELECT (sysdate-365 + (LEVEL -1)) AS DATES
 FROM DUAL connect by level <=( sysdate-(sysdate-365))

if a 'from' and a 'to' date is replaced in place of sysdate and sysdate-365, the output will be a range of dates between the from and to date.

Answer 3

Recently I had a similar problem and solved it with this easy query:

SELECT
  (to_date(:p_to_date,'DD-MM-YYYY') - level + 1) AS day
FROM
  dual
CONNECT BY LEVEL <= (to_date(:p_to_date,'DD-MM-YYYY') - to_date(:p_from_date,'DD-MM-YYYY') + 1);

Example

SELECT
  (to_date('01-05-2015','DD-MM-YYYY') - level + 1) AS day
FROM
  dual
CONNECT BY LEVEL <= (to_date('01-05-2015','DD-MM-YYYY') - to_date('01-04-2015','DD-MM-YYYY') + 1);

Result

01-05-2015 00:00:00
30-04-2015 00:00:00
29-04-2015 00:00:00
28-04-2015 00:00:00
27-04-2015 00:00:00
26-04-2015 00:00:00
25-04-2015 00:00:00
24-04-2015 00:00:00
23-04-2015 00:00:00
22-04-2015 00:00:00
21-04-2015 00:00:00
20-04-2015 00:00:00
19-04-2015 00:00:00
18-04-2015 00:00:00
17-04-2015 00:00:00
16-04-2015 00:00:00
15-04-2015 00:00:00
14-04-2015 00:00:00
13-04-2015 00:00:00
12-04-2015 00:00:00
11-04-2015 00:00:00
10-04-2015 00:00:00
09-04-2015 00:00:00
08-04-2015 00:00:00
07-04-2015 00:00:00
06-04-2015 00:00:00
05-04-2015 00:00:00
04-04-2015 00:00:00
03-04-2015 00:00:00
02-04-2015 00:00:00
01-04-2015 00:00:00

Answer 4

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 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

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)

Answer 7

Date range between 12/31/1996 and 12/31/2020

SELECT dt, to_char(dt, 'MM/DD/YYYY') as date_name, 
  EXTRACT(year from dt) as year, 
  EXTRACT(year from fiscal_dt) as fiscal_year,
  initcap(to_char(dt, 'MON')) as month,
  to_char(dt, 'YYYY')        || ' ' || initcap(to_char(dt, 'MON')) as year_month,
  to_char(fiscal_dt, 'YYYY') || ' ' || initcap(to_char(dt, 'MON')) as fiscal_year_month,
  EXTRACT(year from dt)*100        + EXTRACT(month from dt) as year_month_id,
  EXTRACT(year from fiscal_dt)*100 + EXTRACT(month from fiscal_dt) as fiscal_year_month_id,
  to_char(dt, 'YYYY')        || ' Q' || to_char(dt, 'Q') as quarter,
  to_char(fiscal_dt, 'YYYY') || ' Q' || to_char(fiscal_dt, 'Q') as fiscal_quarter
  --, EXTRACT(day from dt) as day_of_month, to_char(dt, 'YYYY-WW') as week_of_year, to_char(dt, 'D') as day_of_week
  FROM (
    SELECT dt, add_months(dt, 6) as fiscal_dt --starts July 1st
    FROM (
      SELECT TO_DATE('12/31/1996', 'mm/dd/yyyy') + ROWNUM as dt 
      FROM DUAL CONNECT BY ROWNUM < 366 * 30 --30 years
    )
    WHERE dt <= TO_DATE('12/31/2020', 'mm/dd/yyyy')
  )

Answer 8

I had the same requirement - I just use this. User enters the number of days by which he/she wants to limit the calendar range to.

  SELECT DAY, offset
    FROM (SELECT to_char(SYSDATE, 'DD-MON-YYYY') AS DAY, 0 AS offset
            FROM DUAL
          UNION ALL
          SELECT to_char(SYSDATE - rownum, 'DD-MON-YYYY'), rownum
            FROM all_objects d)
            where offset <= &No_of_days

I use the above result set as driving view in LEFT OUTER JOIN with other views involving tables which have dates.

Answer 9

A week from 6 months back

SELECT (date'2015-08-03' + (LEVEL-1)) AS DATES
 FROM DUAL 
 where ROWNUM < 8
 connect by level <= (sysdate-date'2015-08-03'); 

if you omit ROWNUM you get 50 rows only, independent of the value.

Answer 10

Better late than never. Here's a method that I devised (after reading this post) for returning a list of dates that includes: (a) day 1 of of the current month through today, PLUS (b) all dates for the past two months:

select (sysdate +1 - rownum) dt 
from dual 
 connect by rownum <= (sysdate - add_months(sysdate - extract(day from sysdate),-2));

The "-2" is the number of prior full months of dates to include. For example, on July 10th, this SQL returns a list of all dates from May 1 through July 10 - i.e. two full prior months plus the current partial month.

Answer 11

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 12

Another simple way to get 365 days from today would be:

SELECT (TRUNC(sysdate) + (LEVEL-366)) AS DATE_ID
FROM DUAL connect by level <=( (sysdate)-(sysdate-366));

Answer 13

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 14

I do this so often for a scheduling app I work on that I created a pipelined table function. Sometimes I need days, hours or 15 minutes between times. This is not exactly the same function I use, because my code is in a package. However, here, I'm getting days between Jan 1 2020 and Jan 10 2020:

SELECT
    days.date_time
FROM
    table(between_times(TO_DATE('2020-01-01'),TO_DATE('2020-01-10'),(60*24), 'Y')) days

The pipelined function:

function between_times(i_start_time TIMESTAMP, i_end_time TIMESTAMP, i_interval_in_minutes NUMBER, include_end_time VARCHAR2 := 'N')
  RETURN DateTableType  PIPELINED
  AS
    time_counter TIMESTAMP := i_start_time;
  BEGIN
    IF i_start_time IS NULL OR i_end_time IS NULL or i_start_time > i_end_time OR i_interval_in_minutes IS NULL OR
      i_interval_in_minutes <= 0 THEN
        RETURN;
      END IF;
      LOOP

        -- by default does not include end time
        if (include_end_time = 'Y') THEN
          exit when time_counter > i_end_time;
        ELSE
          exit when time_counter >= i_end_time;
        END IF;
        
        
        pipe row(DateType( time_counter ));     
        time_counter := time_counter + i_interval_in_minutes/(60*24);
        
      END LOOP;
      
      EXCEPTION  WHEN NO_DATA_NEEDED THEN NULL;      
  END;

Answer 15

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 16

This query generates a list of dates 4000 days in the future and 5000 in the past as of today (inspired on http://blogs.x2line.com/al/articles/207.aspx):

SELECT * FROM (SELECT
    (CONVERT(SMALLDATETIME, CONVERT(CHAR,GETDATE() ,103)) + 4000 -
                n4.num * 1000 -
                n3.num * 100 -
                n2.num * 10 -
                n1.num) AS Date, 
    year(CONVERT(SMALLDATETIME, CONVERT(CHAR,GETDATE() ,103)) + 4000 -
                n4.num * 1000 -
                n3.num * 100 -
                n2.num * 10 -
                n1.num) as Year,
    month(CONVERT(SMALLDATETIME, CONVERT(CHAR,GETDATE() ,103)) + 4000 -
                n4.num * 1000 -
                n3.num * 100 -
                n2.num * 10 -
                n1.num) as Month,
    day(CONVERT(SMALLDATETIME, CONVERT(CHAR,GETDATE() ,103)) + 4000 -
                n4.num * 1000 -
                n3.num * 100 -
                n2.num * 10 -
                n1.num) as Day
           FROM (SELECT 0 AS num union ALL
                 SELECT 1 UNION ALL
                 SELECT 2 UNION ALL
                 SELECT 3 UNION ALL
                 SELECT 4 UNION ALL
                 SELECT 5 UNION ALL
                 SELECT 6 UNION ALL
                 SELECT 7 UNION ALL
                 SELECT 8 UNION ALL
                 SELECT 9) n1
               ,(SELECT 0 AS num UNION ALL
                 SELECT 1 UNION ALL
                 SELECT 2 UNION ALL
                 SELECT 3 UNION ALL
                 SELECT 4 UNION ALL
                 SELECT 5 UNION ALL
                 SELECT 6 UNION ALL
                 SELECT 7 UNION ALL
                 SELECT 8 UNION ALL
                 SELECT 9) n2
               ,(SELECT 0 AS num union ALL
                 SELECT 1 UNION ALL
                 SELECT 2 UNION ALL
                 SELECT 3 UNION ALL
                 SELECT 4 UNION ALL
                 SELECT 5 UNION ALL
                 SELECT 6 UNION ALL
                 SELECT 7 UNION ALL
                 SELECT 8 UNION ALL
                 SELECT 9) n3  
               ,(SELECT 0 AS num UNION ALL
                 SELECT 1 UNION ALL
                 SELECT 2 UNION ALL
                 SELECT 3 UNION ALL
                 SELECT 4 UNION ALL
                 SELECT 5 UNION ALL
                 SELECT 6 UNION ALL
                 SELECT 7 UNION ALL
                 SELECT 8) n4
        ) GenCalendar  ORDER BY 1