CODEWITHSUNDEEP
mysqlsqldatetimebetween
Nishanth
Nishanth

Reputation: 381

MySQL Select range date between two columns without using table columns

In My sql I want to get the date differences between two dates without using table columns For example

SELECT BETWEEN '2012-01-10' AND '2012-01-15' as date1;

I have to get the following date as output '2012-01-11' '2012-01-12' '2012-01-13' '2012-01-14'

how to write a query to get the output as above

Upvotes: 0

Views: 1162

Answers (2)

Akina
Akina

Reputation: 42632

WITH RECURSIVE
cte AS ( SELECT @startdate + INTERVAL 1 DAY `date`
         UNION ALL
         SELECT `date`+ INTERVAL 1 DAY
         FROM cte 
         WHERE `date` < @enddate - INTERVAL 1 DAY )
SELECT * 
FROM cte

fiddle

MySQL 8+ needed.

my sql version used 5.6

maximum 1 year

SELECT @startdate + INTERVAL (n3.num * 7 * 7 + n2.num * 7 + n1.num + 1) DAY `date`
FROM  ( SELECT 0 num union select 1 union select 2 union select 3
       union select 4 union select 5 union select 6) n1
JOIN  ( SELECT 0 num union select 1 union select 2 union select 3
       union select 4 union select 5 union select 6) n2
JOIN  ( SELECT 0 num union select 1 union select 2 union select 3
       union select 4 union select 5 union select 6 union select 7) n3
HAVING `date` < @enddate;

fiddle

Max. period length is 7 * 7 * 8 = 392 days.

Upvotes: 1

GMB
GMB

Reputation: 222462

If you are running MySQL 8.0, you can do this with a recursive query:

with recursive cte as (
    select '2012-01-10' + interval 1 day as dt
    union all 
    select dt + interval 1 day from cte where dt + interval 1 day < '2012-01-15'
)
select * from cte

This generates one row per date in between the bounds given as input.

Upvotes: 1

Related Questions