Question 288945 in Mysql Question posted in
The official MySQL documentation can be found here.

Generate week list in MYSQL 8

With the query below, I generate rows of the week numbers in a year.

SET @sql = 52;
SELECT WEEKOFYEAR("2023-12-31") INTO @sql;
WITH RECURSIVE weeks(i) AS (
    SELECT 1
    UNION ALL
    SELECT  i + 1
    FROM weeks
    WHERE i + 1 <= @sql
)
SELECT * FROM weeks;

The problem is 2023-12-31,
Imagine in next year 2024-12-31, so

SELECT WEEKOFYEAR("2023-12-31"). ===> 1

So the result will be buggy, coz the first query just have a row.

Tags:

2

Answers


  1. 0

    You can use the WEEK() function to retrieve the week number for a specific date.

    SET @sql = 52;
SELECT WEEK("2024-12-31") INTO @sql;
WITH RECURSIVE weeks(i) AS (
    SELECT 1
    UNION ALL
    SELECT  i + 1
    FROM weeks
    WHERE i + 1 <= @sql
)
SELECT * FROM weeks;
    Login or Signup to reply.
  2. 0

    Update:

    SET @start_date = '2023-12-31';

WITH RECURSIVE WeekNumbersCTE AS (
    SELECT 1 AS week_num, @start_date AS start_date
    UNION
    SELECT week_num + 1, DATE_ADD(start_date, INTERVAL 1 WEEK)
    FROM WeekNumbersCTE
    WHERE week_num < 52
)

SELECT week_num, start_date AS week_start_date, DATE_ADD(start_date, INTERVAL 6 DAY) AS week_end_date
FROM WeekNumbersCTE;

    You can create a CTE and then call it as a function. I am just implementing the CTE here and select query, hope you can use in a way that you want.

    *REMOVED WRONG CODE as it is not striking out in HTML text

    I hope this is the thing you’re looking for. Based on start_date the week number will be dynamically updated.

    Update Output where start_date='2023-12-31'

    +----------+------------------+----------------+
| week_num |  week_start_date |  week_end_date |
+----------+------------------+----------------+
|        1 | 2023-12-31       | 2024-01-06     |
|        2 | 2024-01-07       | 2024-01-13     |
|        3 | 2024-01-14       | 2024-01-20     |
|        4 | 2024-01-21       | 2024-01-27     |
|        5 | 2024-01-28       | 2024-02-03     |
...
|       49 | 2024-12-01       | 2024-12-07     |
|       50 | 2024-12-08       | 2024-12-14     |
|       51 | 2024-12-15       | 2024-12-21     |
|       52 | 2024-12-22       | 2024-12-28     |
+----------+------------------+----------------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search