Kalenderwochen in T-SQL

Vorbemerkungen

Möchte man in T-SQL ähnlichen Komfort für Datums-Funktionen haben, wie z.B. in C#, so scheitert man immer mal wieder an scheinbar einfachen Aufgaben. Meine Aufgabe war es, die Datumswerte einer Kalenderwoche (also 7 Stück) über ein Resultset verfügbar zu machen. Fangen wir also zur Erklärung am besten mit dem Ergebnis an:

Man kann also erkennen, dass es etwas gibt, dass GetWeekdaysForWeek heißt (bei mir wird das eine Tabellenwert-Funktion), dass sich dies in einem Schema „Utils“ befindet und dass es ein Datum entgegen nimmt. Als Ergebnis bekomme ich ein Resultset, dass als einzige Spalte „DayValue“ anbietet und das immer genau 7 Zeilen liefert. Jede Zeile entspricht hier einem Tag der Kalenderwoche (Ich habe die Abfrage übrigens am 14.07.2013 ausgeführt).

Wer sich das Beispiel genauer ansieht, wird feststellen, dass Montage als erster Tag der Woche angesehen werden. Eine weitere Anforderung ist also, dass mein Tool mit ISO-Datumsfunktionen klar kommt und nicht das amerikanische Modell fährt.

Theorie

Wo ist hier aber nun das Problem? Das Problem sind ein paar Einschränkungen in den T-SQL-Funktionen. Nähern wir uns dem Thema mit ein wenig Grundlagen. Wir könnten uns der Lösung von 2 Seiten her nähern. Die eine wäre, den aktuellen Tagesoffset zu nehmen (z.B. 4, wenn heute ein Donnerstag wäre) und dann die Differenz zum Monat (also 3) zu errechnen. Wir könnten dann ein Result in einer Schleife aufbauen. Man merkt eigentlich bereits beim Lesen, dass das nicht so der Hammer ist und einige Unwägbarkeiten versteckt sind.

Die andere Lösung wäre es, die Kalenderwochen-Nummer zu ermitteln und dann irgendwie rauszubekommen, was der erste Tag dieser Woche ist. Das ist eigentlich der intuitivere Ansatz und war daher auch mein Ziel.

Schritt 1: Kalenderwoche ermitteln

Zunächst möchte ich also die Wochennummer der zu einem Datum (wir nehmen mal GETDATE(), also Heute) ermitteln:

SELECT GETDATE() AS Heute, DATEPART(wk, GETDATE()) AS KW;

Ergebnis:

Heute                   KW
----------------------- -----------
2013-07-14 15:30:06.540 29

Gut, dass heute ausgerechnet der 14.07.2013 und damit ein Sonntag ist. Das Ergebnis stimmt nämlich nicht, wei ein Blick in den Outlook-Kalender zeigt:

Profis wissen natürlich sofort, dass die Differenz daher rührt, dass ein amerikanisches Produkt, wie SQL Server standardmäßig immer wie „zu Hause“ rechnet. In den USA ist die KW eines Jahres immer die mit dem 01. Januar. Bei uns wird ISO benutzt, und das heißt grob, dass die Woche mit dem ersten Donnerstag die Kalenderwoche 1 ist. (oder anders die Woche, die als erste mindestens 4 Tage im neuen Jahr hat).

Dafür hat T-SQL mittlerweile etwas an Bord:

SELECT GETDATE() AS Heute, DATEPART(isowk, GETDATE()) AS KW;

Ergebnis:

Heute                   KW
----------------------- -----------
2013-07-14 15:30:06.540 28

Soweit, so gut. Doch, wie komme ich nun an den ersten Tag (also den Montag) dieser Woche?

Schritt 2: Den Montag ermitteln

Ich hatte die Idee, eine Kombination der Funktionen DATEDIFF und DATEADD zu nutzen. Sehen wir uns mal folgendes an.

SELECT DATEDIFF(ww, '0001-01-01', GETDATE()) AS 'Wochen seit 01.01.0001';

Ergebnis:

Wochen seit 01.01.0001
----------------------
5924 

Seit dem 01.01.0001 sind also insgesamt 5924 Wochen vergangen. Wenn ich dieses Ergebnis nun wiederum an DATEADD gebe:

SELECT DATEADD(ww, DATEDIFF(ww,'0001-01-01', GETDATE()), '0001-01-01') AS Montag;

Ergebnis:

Montag
-----------------------
2013-07-15 00:00:00.000

bekomme ich ein Datum zurück, weil DATEADD als dritten Parameter die 0 bekommen hat. Diese steht in diesem Fall für den Anfang der Zeitzählung. Der SQL-Server wird also von Sekunde 0 an 5924 Wochen dazurechnen und landet in der ersten Sekunde des Montags. Das blöde ist nur, dass es der falsche Montag ist, weil hier ja nun meine ISO-Zählung nicht mit drin ist.

Schritt 3: ISO-Erkenntnisse nutzen

Der nächste Schritt ist also, dem SQL-Server unsere in Schritt 1 gesammelten Erkenntnisse nun wieder „überzuhelfen“:

SELECT DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - (DATEPART(wk, GETDATE()) - DATEPART(isowk, GETDATE())), 0) AS Montag;

Ergebnis:

Montag
-----------------------
2013-07-08 00:00:00.000

Der Trick ist ganz einfach der, dass wir dem Null-Datum nicht einfach nur die vergangenen Wochen, sondern die verganenen Wochen abzüglich der Wochendifferenz zwischen ISO- und NichtISO-Berechnung übergeben.

Schritt 4: Ein Resultset aufbauen

Der Rest ist nun relativ simpel und macht sich eine UNION-Abfrage zunutze:

WITH WeekDays AS
(
    SELECT DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - (DATEPART(wk, GETDATE()) - DATEPART(isowk, GETDATE())), 0) DayValue
    UNION ALL
    SELECT  DayValue + 1
    FROM    WeekDays
    WHERE   DayValue + 1 <= DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - (DATEPART(wk, GETDATE()) - DATEPART(isowk, GETDATE())), 6)
)
SELECT DayValue
FROM WeekDays

Wir bauen zunächst eine Pseudo-Tabelle „WeekDays“ auf. Sie beginnt mit dem Montag und verknüpft diesen per UNION mit allen folgenden 6 Tagen. Zum Schluss wird das Ergebnis als ein Result-Set von „WeekDays“ ermittelt.

DayValue
-----------------------
2013-07-08 00:00:00.000
2013-07-09 00:00:00.000
2013-07-10 00:00:00.000
2013-07-11 00:00:00.000
2013-07-12 00:00:00.000
2013-07-13 00:00:00.000
2013-07-14 00:00:00.000

Eine Tabellenwert-Funktion rundet das ganze dann wiederverwendbar ab:

CREATE FUNCTION [Util].[GetWeekdaysForWeek]
(
    -- Add the parameters for the function here
    @dayInWeek datetime
)
RETURNS TABLE
AS
RETURN
(
    WITH WeekDays AS
    (
        SELECT DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - (DATEPART(wk, GETDATE()) - DATEPART(isowk, GETDATE())), 0) DayValue
        UNION ALL
        SELECT  DayValue + 1
        FROM    WeekDays
        WHERE   DayValue + 1 <= DATEADD(ww, DATEDIFF(ww, 0, GETDATE()) - (DATEPART(wk, GETDATE()) - DATEPART(isowk, GETDATE())), 6)
    )
    SELECT DayValue
    FROM WeekDays
)

Nachdem man dieses SQL ausgeführt hat (bitte beim Schema „[Util]“ aufpassen und dieses entweder anpassen oder in der eigenen DB verfügbar machen), kann man zukünftig per

SELECT * FROM [Util].[GetWeekdaysForWeek](GETDATE());

schnell das richtige Ergebnis ausgeben und benutzen.

Einsatzzweck

Wer sich nun immer noch fragt, wofür man diesen ganzen Aufwand eigentlich braucht, der sei darauf verwiesen, dass man öfter mal eine Auswertung über einen Zeitraum fahren möchte. Ein Diagramm, wie das folgende

kann nun direkt über ein T-SQL-Kommando befüllt werden, was ohne die hier gezeigten Tricks eher schwierig wäre.