0 votes
1 view
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.4k points)

The below solution uses no procedures, loops, or temp tables. Here, the subquery generates dates for the last 10,000 days and could be extended to go as far back or forward as you wish.

Use the below code:

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' 

The output is as follows:

Date

----------

2010-01-24

2010-01-23

2010-01-22

2010-01-21

2010-01-20

Notes: The testing result is like this, the performance is good: the above query takes 0.0009 sec. But, if you are extending 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 Jul 15, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...