MySQL Wochennummer Liste

MySql Skript zur Erstellung einer Wochennummer Tabelle

Da je nach Datenbank, Programmiersprache, Systemeinstellungen die Wochennummern verschieden gezählt werden können, ist es üblich bei Anwendungen welche Einträge auf Wochenbasis bearbeiten / planen müssen, eine Tabelle mit Einträgen zu erfassen, welche die Wochennummern, Start- und Enddatum eines oder mehrerer Jahre beinhalten. Um dies vereinfacht zu machen und nicht alles manuell einzugeben, habe ich ein SQL Skript für MySQL erstellt, welches für ein gewisses Intervall (maximal ca. 300 Jahre), ebendiese Informationen ausgibt.

SELECT week_no, 
       year_no, 
       first_date, 
       last_date 
FROM  (SELECT WEEKOFYEAR(selected_date)                                     AS 
              week_no 
                    , 
              CASE 
                WHEN YEAR(SUBDATE(selected_date, INTERVAL WEEKDAY(selected_date) 
                                                 day)) 
                     != YEAR 
                     ( 
                            ADDDATE(selected_date, 
                            INTERVAL 6-WEEKDAY(selected_date) 
                            day)) 
                     AND WEEKOFYEAR(SUBDATE(selected_date, 
                                    INTERVAL WEEKDAY(selected_date) day 
                                    )) != 1 THEN YEAR(SUBDATE(selected_date, 
                INTERVAL WEEKDAY(selected_date) day)) 
                ELSE YEAR(ADDDATE(selected_date, 
                          INTERVAL 6-WEEKDAY(selected_date) day 
                          )) 
              end                                                           AS 
              year_no 
                    , 
              SUBDATE(selected_date, INTERVAL WEEKDAY(selected_date) 
                                     day)   AS first_date, 
              ADDDATE(selected_date, INTERVAL 6-WEEKDAY(selected_date) day) AS 
                    last_date 
       FROM   (SELECT * 
               FROM   (SELECT ADDDATE('1970-01-01', T4.i * 10000 + T3.i * 1000 + 
                                                    T2.i * 100 + 
                                                            T1.i * 10 + 
                              T0.i) selected_date 
                       FROM   (SELECT 0 i UNION 
                               SELECT 1 UNION 
                               SELECT 2 UNION 
                               SELECT 3 UNION 
                               SELECT 4 UNION 
                               SELECT 5 UNION 
                               SELECT 6 UNION 
                               SELECT 7 UNION 
                               SELECT 8 UNION 
                               SELECT 9) T0, 
                              (SELECT 0 i UNION 
                               SELECT 1 UNION 
                               SELECT 2 UNION 
                               SELECT 3 UNION 
                               SELECT 4 UNION 
                               SELECT 5 UNION 
                               SELECT 6 UNION 
                               SELECT 7 UNION 
                               SELECT 8 UNION 
                               SELECT 9) T1, 
                              (SELECT 0 i UNION 
                               SELECT 1 UNION 
                               SELECT 2 UNION 
                               SELECT 3 UNION 
                               SELECT 4 UNION 
                               SELECT 5 UNION 
                               SELECT 6 UNION 
                               SELECT 7 UNION 
                               SELECT 8 UNION 
                               SELECT 9) T2, 
                              (SELECT 0 i UNION 
                               SELECT 1 UNION 
                               SELECT 2 UNION 
                               SELECT 3 UNION 
                               SELECT 4 UNION 
                               SELECT 5 UNION 
                               SELECT 6 UNION 
                               SELECT 7 UNION 
                               SELECT 8 UNION 
                               SELECT 9) T3, 
                              (SELECT 0 i UNION 
                               SELECT 1 UNION 
                               SELECT 2 UNION 
                               SELECT 3 UNION 
                               SELECT 4 UNION 
                               SELECT 5 UNION 
                               SELECT 6 UNION 
                               SELECT 7 UNION 
                               SELECT 8 UNION 
                               SELECT 9) T4) V 
               WHERE  selected_date BETWEEN '2016-01-01' AND '2239-12-31') DATES 
      ) 
      WDATES 
GROUP  BY year_no, 
          week_no, 
          first_date, 
          last_date 
ORDER  BY year_no, 
          week_no

Leave a Reply