a WITH workaround for MySQL

How to convert this with part to MySQL?

SET @start  = '20210101';
SET @end  = '20211231';
        
WITH cte AS   
(  
    SELECT dt = DATEADD(DAY, -(DAY(@start) - 1), @start)  
    UNION ALL  
    SELECT DATEADD(MONTH, 1, dt)  
    FROM cte  
    WHERE dt < DATEADD(DAY, -(DAY(@end) - 1), @end)  
)
SELECT DATENAME(MONTH,dt) Name, DAY(EOMONTH(dt)) as Days into RESULT_TABLE
FROM cte

P.S. as far as I know I cant use WITH in MySQL since version 8.0. But what about version prior to 8.0?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

In mysql 8 or mariadb 10.2+, you would do this like:

WITH RECURSIVE cte AS (
    SELECT DATE_SUB(@start, INTERVAL DAY(@start) - 1 DAY) AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 MONTH)
    FROM cte
    WHERE dt < DATE_SUB(@end, INTERVAL DAY(@end) - 1 DAY)
)
SELECT MONTHNAME(dt) AS Name, DAY(LAST_DAY(dt)) AS Days
FROM cte;

But I’m not sure I understand what the “into RESULT_TABLE” was doing.

Method 2

You can create a table from a recursive query this way:

SET @start  = '20210101';
SET @end  = '20211231';

CREATE TABLE result_table AS
        
WITH RECURSIVE cte(dt) AS   
(  
    SELECT DATE(@start)
    
    UNION ALL  
    
    SELECT DATE_ADD(dt, INTERVAL 1 MONTH)
    FROM cte  
    WHERE DATE_ADD(dt, INTERVAL 1 MONTH) < @end  
)

SELECT MONTHNAME(dt) AS Name, DAY(LAST_DAY(dt)) AS Days
FROM cte;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0