Kalenderwochen in T-SQL

Da war wieder mal so ein scheinbr billiges Problem, das mir ein wenig Kopfzerbrechen bereitet hat. Die Lösung könnte dem ein oder anderen bei Abfragen im SQL-Server helfen.

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:

Abb. 1: So soll es mal funktionieren
Abb. 1: So soll es mal funktionieren

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;

 

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:

Abb. 2: Outlook-Kalender
Abb. 2: Outlook-Kalender

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;

 

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, 0, GETDATE()) AS 'Wochen seit 01.01.0001'
<pre>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, 0, GETDATE()), 0) AS Montag
<pre>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.

EDIT: Bernhard hat in seinen Kommentaren auf einen Fehler in der Logik hingewiesen. Der Fehler ist, dass ich als Anfangsdatum „0“ statt „0001-01-01“ verwende. Das löst zwar das Montagsproblem nicht, führt aber zu anderen Ergebnissen. Die Listings 3 und 4 müssten also so aussehen:

SELECT DATEDIFF(ww, '0001-01-01', GETDATE()) AS 'Wochen seit 01.01.0001';
SELECT DATEADD(ww, DATEDIFF(ww,'0001-01-01', GETDATE()), '0001-01-01') AS Montag;</pre>
<pre>

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

 

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

Abb. 3: Einsatz-Szenario
Abb. 3: Einsatz-Szenario

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

5 Antworten auf „Kalenderwochen in T-SQL“

  1. Leider entspricht die Lösung nicht so wirklich der Anforderung „Meine Aufgabe war es, die Datumswerte einer Kalenderwoche (also 7 Stück) über ein Resultset verfügbar zu machen“, da sie lediglich die 7 Datumswerte der aktuellen Woche zruückliefert. Dazu bedarf es keiner Wochenumrechnung, hier reicht die Wochentagsfunktion aus.
    Ich hatte aus dem Text des Artikels erwartet, dass die Funktion als Parameter eine beliebige Woche inkl. Jahr übernimmt und dazu den Datumswerte liefert.

    MfG, Chris

  2. Zitat: Seit dem 01.01.0001 sind also insgesamt 5924 Wochen vergangen.

    Schonmal nachgerechnet? 5924 Wochen entsprechen pi mal Daumen 114 Jahre. Wenn du diese 114 Jahre auf den 01.01.0000 aufrechnest, bist du noch nicht mal im Mittelalter ;-)
    Warum ist das Ergebnis falsch?
    Wenn du auf dem SQL Server bei Datetime von 0 wegrechnest, ist 0 mit dem 01.01.1990 00:00:00.000 definiert, sprich einem Unix-Timestamp.

    Wie lässt sich das beweisen?
    SELECT DATEADD(WEEK, 5924, 0)

  3. Hallo!

    Das „Datum 0“ ist, wie schon bemerkt wurde, der 1. Januar 1900. Was für ein Glück, es ist ein Montag!
    Durch ganzzahlige Division (also ohne Rest) der Tagesdifferenz lässt sich damit eine Anzahl von Wochen ermitteln, und so kann man ein gegebenes @Datum auf einen Montag „runden“:

    DATEADD(week, DATEDIFF(day, 0, @Datum) / 7, 0) AS Montag

    Ersetzt man die letzte Null durch eine 3, so landet man auf dem Donnerstag der Woche, die das @Datum enthält:

    DATEADD(week, DATEDIFF(day, 0, @Datum) / 7, 3) AS Donnerstag

    Die Jahreszahl dieses Donnerstags ist das „Kalenderjahr“ (das Jahr, zu dem unsere ISO-Kalenderwoche gehört):
    YEAR(DATEADD(week, DATEDIFF(day, 0, @Datum) / 7, 3)) AS Kalenderjahr

    Viele Grüße
    Wolfgang

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

This site uses Akismet to reduce spam. Learn how your comment data is processed.