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.
The syntax error is because the example is written for SQL Server, not MySQL. It requires some tweaking to work with MySQL 8.x:
You don't need
DECLARE
User-defined variables. Simply useSET
to declare and assign variable valuesDATEADD()
is a SQL Server function. The MySQL equivalent is DATE_ADD(Date,INTERVAL expression unit)DATEPART(weekday,...)
is a SQL Server function. For MySQL, try DAYOFWEEK(date)Finally, use the keyword
RECURSIVE
in the CTE. From Documentation: p>SQL