Mysql get date and date range for specific date (syntax error)
P粉637866931
P粉637866931 2024-02-25 19:51:13
0
1
399

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.

P粉637866931
P粉637866931

reply all(1)
P粉575055974

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: p>

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 

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

dbviolinhere p>

Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template