Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

I would like to run a query like

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

And return data like:

days

----------

2010-01-20

2010-01-21

2010-01-22

2010-01-23

2010-01-24

1 Answer

0 votes
by (40.7k points)

The given solution uses no loops, procedures, or temp tables. And, the subquery generates dates for the last 10,000 days, and can be extended to go as far back or forward as you want:

select a.Date 

from (

    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.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

    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 d

) a

where a.Date between '2010-01-20' and '2010-01-24' 

Output:

Date

----------

2010-01-24

2010-01-23

2010-01-22

2010-01-21

2010-01-20

Notes on Performance: Testing it out here, according to the above query it takes 0.0009 sec. But, If you want to extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it'll run in 0.0458 sec.

Related questions

0 votes
1 answer
asked Jul 19, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Jul 17, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Jul 17, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
asked Jan 5, 2021 in SQL by Appu (6.1k points)

Browse Categories

...