Mysql get date and date range for specific date (syntax error)

Question

This is my complete code, but when I launch it,

DECLARE @StartDateTime DATETIME
DECLARE @EndDateTime DATETIME

SET @StartDateTime = '2022-04-01'
SET @EndDateTime = '2022-04-29';

WITH DateRange(Dates, DateWD) AS 
(
    SELECT @StartDateTime as Date, DATEPART(WEEKDAY, @StartDateTime) 
    UNION ALL
    SELECT DATEADD(d,1,Dates), DATEPART(WEEKDAY, DATEADD(d,1,Dates)) 
    FROM DateRange 
    WHERE Dates < @EndDateTime
)

SELECT Dates, DateWD
FROM DateRange 
 WHERE DATEWD NOT IN(1,7) AND Dates NOT IN(
 SELECT (HOLI_YEAR + '-' + HOLI_MONTH + '-' + HOLI_DAY) AS DATE
 FROM TB_HOLIDAY_CODE

OPTION (MAXRECURSION 0)

This error occurs.

I want to display a list of dates in April 2022 (except Sundays and Saturdays)

Example) The start date is 2022-04-01 End date April 30, 2022

The results are out->

date	DateWD
2022-04-01	（Friday）
2022-04-04	(on Monday)
2022-04-05	(Tuesday)
2022-04-06	(Wednesday)
2022-04-07	(Thursday)
2022-04-08	（Friday）
2022-04-11	(on Monday)
....	...

How to fix this code? please help me. Thanks

*** I don't know how to use tables. Because I don't have a table and I just want to use SQL QUERY.

Answer 1

The syntax error is because the example is written for SQL Server, not MySQL. It requires some tweaking to work with MySQL 8.x:

1. You don't need DECLARE User-defined variables. Simply use SET to declare and assign variable values

2. DATEADD() is a SQL Server function. The MySQL equivalent is DATE_ADD(Date,INTERVAL expression unit)

3. DATEPART(weekday,...) is a SQL Server function. For MySQL, try DAYOFWEEK(date)

4. Finally, use the keyword RECURSIVE in the CTE. From Documentation:

SQL

SET @StartDateTime = '2022-04-01';
SET @EndDateTime = '2022-04-29';


WITH RECURSIVE DateRange(Dates, DateWD) AS
(
    SELECT @StartDateTime, DayOfWeek(@StartDateTime)
    UNION ALL
    SELECT DATE_ADD(Dates, INTERVAL 1 DAY), DayOfWeek(DATE_ADD(Dates, INTERVAL 1 DAY))
    FROM DateRange
    WHERE Dates < @EndDateTime
)
SELECT * 
FROM   DateRange
WHERE  DateWDNOT IN(1,7)

result:

date	DateWD
2022-04-01	6
2022-04-04	2
2022-04-05	3
2022-04-06	4
2022-04-07	5
2022-04-08	6
2022-04-11	2
2022-04-12	3
2022-04-13	4
2022-04-14	5
2022-04-15	6
2022-04-18	2
2022-04-19	3
2022-04-20	4
2022-04-21	5
2022-04-22	6
2022-04-25	2
2022-04-26	3
2022-04-27	4
2022-04-28	5
2022-04-29	6

db<>violinhere