Generate days from date range sql

Question

I need to generate the days between two dates in sql like this:

Im using this query:

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a 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) as a
    cross join (select 0 as a 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) as b
    cross join (select 0 as a 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) as c
) a

Works well but this generates always 1000 days. How can i generate any other number of days like 365?

Does this answer your question? [Get a list of dates between two dates](https://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) — outis, Mar 20 '22 at 22:38

score 0 · Accepted Answer · answered Feb 10 '17 at 17:52

You could add a limit clause (and an order by)

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a 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) as a
    cross join (select 0 as a 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) as b
    cross join (select 0 as a 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) as c
) a 
order by 1 desc 
limit 365

score 0 · Answer 2 · answered Feb 10 '17 at 18:38

Or, if you are allowed to create temporary tables, in a more readable fashion - and extendable to 10,000 integers or more quite easily:

CREATE TEMPORARY TABLE units(idx) 
ENGINE=MEMORY 
AS (
          SELECT 0
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
);

SELECT a.the_date 
FROM (
SELECT curdate() - INTERVAL (hundreds + tens + units) DAY AS the_date
FROM       (SELECT idx      AS units    FROM units) units
CROSS JOIN (SELECT idx*  10 AS tens     FROM units) tens
CROSS JOIN (SELECT idx* 100 AS hundreds FROM units) hundreds
WHERE hundreds + tens + units < 365
) AS a
ORDER BY 1
;

Generate days from date range sql

2 Answers2