Wie man den ersten Tag der Woche in SQL Server ermitteln kann

Dies ist zusätzlicher Inhalt für den LearnSQL.de Kurs Kundenverhaltensanalyse in SQL Server.

Im vorherigen Artikel haben wir darüber gesprochen, wie Sie die SQL Server-Funktion DATEPART() mit Woche oder iso_week verwenden können, um Ereignisse nach Woche zu gruppieren. Die Abfrage könnte wie folgt aussehen:

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Das Ergebnis sieht etwa so aus:

Die Abfrage zeigt die Wochennummer als Bezeichnung für die Woche an. Diese Bezeichnung ist nicht sehr hilfreich. Woher wissen wir schließlich, was "Woche 22" bedeutet? Liegt sie im April, Mai oder Juni? Besser ist es, zu jeder Woche ein Datum anzuzeigen, z. B. den ersten Tag.

In diesem Artikel zeigen wir Ihnen, wie Sie den ersten Tag der Woche in SQL Server ermitteln können. Der erste Schritt: eine Abkürzung.

Der Hack: Verwendung der Funktion MIN()

Bevor wir uns mit den richtigen Methoden zur Berechnung des ersten Tages der Woche in SQL Server befassen, wollen wir einen Trick vorstellen, mit dem Sie eine lesbare Beschriftung für eine Woche anzeigen können - die Funktion MIN():

SELECT
  DATEPART(week, RegistrationDate) AS Week,
  MIN(RegistrationDate) as WeekStart,
  COUNT(CustomerID) AS Registrations
FROM Customers
WHERE '20180101' <= RegistrationDate
  AND RegistrationDate < '20190101'
GROUP BY DATEPART(week, RegistrationDate)
ORDER BY DATEPART(week, RegistrationDate);

Das Ergebnis sieht dann so aus:

Diese Abfrage zeigt die Nummer und das minimale Registrierungsdatum jeder Woche an (mit anderen Worten, den ersten Tag der Woche). Natürlich ist diese Lösung keineswegs perfekt; sie geht davon aus, dass die Ereignisse, die Sie zählen (in diesem Fall die Anmeldungen), jeden Tag stattfinden. Wenn es am ersten Tag der Woche keine Anmeldungen gibt, zeigt Ihnen die Abfrage den zweiten Tag der Woche als WeekStart an.

Diese Lösung mag ausreichen, wenn Sie einfach interaktiv mit den Daten arbeiten und eine ungefähre Angabe des Zeitpunkts eines Ereignisses benötigen. Wenn Sie jedoch das genaue Datum für den ersten Tag der Woche benötigen, reicht dies nicht aus. Versuchen wir etwas anderes.

Wie berechnet man Wochenbezeichnungen in SQL Server?

Es gibt zwei gängige Möglichkeiten, den ersten Tag der Woche zu definieren: Sonntag (typischerweise in den USA verwendet) und Montag (typischerweise in Europa verwendet). Wir beginnen damit, zu erörtern, wie Sie den ersten Tag der Woche in einem dieser beiden Stile finden können.

Option 1: Sonntag als erster Tag der Woche

Wir beginnen mit dem Sonntag, da er leichter zu erklären ist. Hier ist der Ausdruck:

DATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday;

Die Funktion DATEADD() nimmt drei Argumente entgegen: ein Datumsteil, eine Zahl und ein Datum. Sie fügt dann dem angegebenen Datumsteil eines eingegebenen Datumswertes einen bestimmten Zahlenwert hinzu und gibt anschließend den geänderten Wert zurück.

Im obigen Ausdruck fügen wir dem Datum -1 eine bestimmte Anzahl von Wochen hinzu. Was bedeutet das? Nun, das Datum 0 ist Mitternacht am 1. Januar 1900, was zufällig ein Montag ist. Das Datum -1 ist also Sonntag, der 31. Dezember 1899. Der obige Ausdruck addiert eine bestimmte Anzahl von Wochen zu diesem Datum.

Das Zahlenargument in unserem Ausdruck wird mit der Funktion DATEDIFF() berechnet. DATEDIFF() benötigt ebenfalls drei Argumente: das Datumsteil, das Startdatum und das Enddatum. Sie gibt die Anzahl der angegebenen Datumsabschnitte zwischen dem Startdatum und dem Enddatum zurück. Wenn DATEDIFF() mit dem Argument Woche verwendet wird, funktioniert es unabhängig von der Einstellung DATEFIRST: Es wird immer der Sonntag als erster Tag der Woche verwendet.

Der Ausdruck DATEDIFF(week, -1, RegistrationDate) berechnet die Anzahl der Wochen zwischen Sonntag, dem 31. Dezember 1899 (dem Datum -1) und dem RegistrationDate.

Dieser Ausdruck:

DATEADD(week, DATEDIFF(week, -1, RegistrationDate), -1) AS Sunday

... nimmt die Anzahl der Wochen zwischen Sonntag, dem 31. Dezember 1899, und dem Registrierungsdatum, addiert diese Zahl zu diesem Sonntag und gibt schließlich den Sonntag zurück (mit anderen Worten, den Beginn der Woche, in der die Registrierung erfolgte).

Option 2: Montag als erster Tag der Woche

Schauen wir uns nun einen Ausdruck an, der Montag als ersten Tag der Woche zurückgibt:

SELECT DATEADD(week, DATEDIFF(week, 0, RegistrationDate - 1), 0) AS Monday;

In dem obigen Ausdruck wird die angegebene Anzahl von Wochen zum Datum 0 addiert. Wie Sie sich erinnern, steht 0 für Mitternacht am Montag, den 1. Januar 1900.

Die Funktion DATEDIFF() behandelt den Sonntag als den ersten Tag der Woche, unabhängig von der Einstellung DATEFIRST. Dies ist der Kalender für Januar 1900, den DATEDIFF() verwendet:

Der Ausdruck DATEDIFF(week, 0, RegistrationDate - 1) berechnet die Anzahl der Wochen zwischen Montag, dem 1. Januar 1900 (Datum 0) und einem Tag vor dem RegistrationDate. Die Verschiebung um einen Tag nach hinten ist erforderlich, weil DATEDIFF() den Sonntag als ersten Tag der Woche verwendet.

Betrachten wir als Beispiel den Sonntag, 7. Januar 1900. Der Ausdruck DATEDIFF(week, 0, '19000107') gibt 1 zurück; wenn Sie den Sonntag als ersten Tag der Woche betrachten, liegt der 7. Januar in Woche 2. Wir möchten jedoch, dass der 7. Januar so behandelt wird, als ob er in Woche 1 läge - als ob Montag der erste Tag der Woche wäre. Daher müssen wir um einen Tag "zurückgehen", um die richtige Anzahl von Wochen für ein Datum zu erhalten.

Letztendlich erhalten wir den folgenden Ausdruck:

SELECT DATEADD(week, DATEDIFF(week, 0, RegistrationDate - 1), 0) AS Monday;

BONUS: Wie man den ersten Tag der Woche in Abhängigkeit von der Einstellung DATEFIRST berechnet

Wenn Sie einen Ausdruck sehen wollen, der für alle DATEFIRST Einstellungen korrekt funktioniert, habe ich schlechte Nachrichten für Sie: Er ist nicht schön. In der Praxis werden Sie wahrscheinlich nur Montag oder Sonntag als ersten Tag der Woche betrachten. Der Vollständigkeit halber möchte ich Ihnen hier einen Ausdruck vorstellen, der unabhängig von DATEFIRST korrekt funktioniert:

SELECT DATEADD(week, 
  DATEDIFF(week, (@@DATEFIRST-8)%7, RegistrationDate-(@@DATEFIRST % 7)), 
  (@@DATEFIRST-8)%7) AS DatefirstAsFirstDayOfWeek;

Zunächst sei erklärt, dass @@DATEFIRST-8)%7 den Wert von DATEFIRST in den entsprechenden Wochentag zum Jahreswechsel 1899/1900 übersetzt. (Sie könnten sich einen anderen Ausdruck ausdenken, der denselben Wert berechnet.) Hier ist eine Tabelle, die Ihnen zeigt, wie viele Tage seit diesem Zeitpunkt vergangen sind:

Der andere Ausdruck lautet

DATEDIFF(week, (@@DATEFIRST-8)%7, RegistrationDate - (@@DATEFIRST % 7)). 

Dieser Ausdruck berechnet die Differenz zwischen dem von uns gewählten Wochentag und dem Registrierungsdatum.

Da DATEDIFF() den Sonntag als ersten Tag der Woche verwendet, müssen wir den Wert von DATEFIRST vom Anmeldedatum abziehen. Wir "verschieben" die Tage zurück, die laut DATEDIFF() in die folgende Woche fallen.

Für DATEFIRST = 3 (Mittwoch) müssen also Sonntag, Montag und Dienstag in die vorangehende Woche "zurückgeschoben" werden, damit DATEDIFF() so funktioniert, wie wir es beabsichtigen.