TSQL: der 1. des Monats

In verschiedenen Projekten stand ich vor der Herausforderung einen bestimmten Tag eines Monats, wie bspw. den 1. aus einem Datum mit TSQL zu bestimmen. Im TSQL gibt es dazu eine Vielzahl von Möglichkeiten, von denen ich Euch einige vorstellen möchte. Die Beispiele führe ich in Einzelschritten aus, so dass dies für jeden nachvollziehbar sind. Als Basis habe ich eine Variable @x mit einem beliebigen Datum (22. September 2018) definiert –> declare @x date = ‚20180922‘;

Hinweis: In den folgenden Beispielen verwende ich das Date-Format, das für diesen Anwendungsfall ideal ist. Dieses Format wurde jedoch erst mit SQL Server 2008 eingeführt. Sollten Sie jedoch noch ältere SQL Server-Versionen im Einsatz haben, empfehle ich Ihnen dringend auf eine neuere Version zu migrieren. Alternativ können Sie jedoch auch das DATETIME-Format verwenden.

  1. Die einfachste Möglichkeit, die auch mit allen SQL Server-Versionen funktioniert, ist sicherlich die Konvertierung des Datum in ein vorgegebenes Zeichenkettenformat, Abschneiden und Ersetzen des Wertes für den Tag und anschließend wieder zurück konvertieren in ein Datumsformat. Die Syntax dafür sieht so aus:

    Select @x
        , CONVERT(varchar(12),@x,112)
        , SUBSTRING(CONVERT(varchar(12),@x,112),0,7)
        , cast(SUBSTRING(CONVERT(varchar(12),@x,112),0,7)+ ’01‘ as date);

    Der Nachteil dieser Vorgehensweise besteht jedoch in den vielen einzelnen Schritten, die sich nachteilig auf die Performance auswirken.

  2. Ein etwas ungewöhnlicher Weg besteht darin, das Datum in die einzelnen Bestandteile zu zerlegen und anschließend wieder zusammenzusetzen:
    Select @x
        , DATEPART(month, @x ), DATEPART(YEAR, @x )
        , DATEPART(month, @x )*100 + DATEPART(year, @X)*10000 + 1
        , cast(cast(DATEPART(month, @x )*100 + DATEPART(year, @X)*10000 + 1 as varchar(8)) as date)Auch bei dieser Lösung sind wieder viele einzelne Schritte erforderlich, wobei der Datumswert in diesem Beispiel in den Zwischenschritten als Integer verarbeitet wird, was sich gegenüber der vorhergehenden Lösung positiv auf die Performance auswirkt.
  3. Eine Lösung ohne Zwischenschritte ist die Anzahl der Tage des Datums von dem Datum abzuziehen:
    Select @x
        , DAY(@x)
    , DateAdd(day, 1DAY(@x), @x)Diese Vorgehensweise ist sehr elegant und, wie wir später sehen werden, auch sehr performant.
  4. Bei diesem Beispiel lass ich mir den letzten Tag des Vormonats geben und addiere einen Tag hinzu:
    Select @x
        , EOMONTH(@x,-1)
        , DateAdd(day, 1, EOMONTH(@x,-1));Auch in dieser Lösung kommen wir mit wenigen Schritten aus und auch die Performance ist sehr gut.
    Hinweis: Die Funktion EOMonth ist erst ab SQL Server 2012 verfügbar.
  5. Im nächsten Beispiel zerlege ich das Datum in Jahr und Monat und setze den Tag auf den vordefinierten Wert (1):
    Select @x
        , year(@x), month(@x)
        , DATEFROMPARTS(year(@x), month(@x), 1);
    Obwohl wir auch in diesem Beispiel nur wenige Schritte benötigen, werden wir später sehen, dass die Performance nicht optimal ist.
    Hinweis: Die Funktion DATEFROMPARTS ist erst ab SQL Server 2012 verfügbar.
  6. Nun verwende eine Funktion zur Formatierung eines Wertes entsprechend einem vorgegebenen Pattern – in diesem Fall yyyy für Jahr (vierstellig), MM für Monat (zweistellig) und 01 für den ersten Tag des Monats. Anschließend wandle ich die Zeichenkette wieder in einen Datumswert um:
    Select @x
        , Format(@x,‚yyyyMM01‘)
        , cast(Format(@x,‚yyyyMM01‘) as date);Die Format-Funktion ist sehr mächtig, da sie eine Vielzahl von Ausgaben ermöglicht. Leider wirkt sich diese Anzahl von Möglichkeiten auch negativ auf die Performance aus.
    Hinweis: Die Funktion FORMAT ist erst ab SQL Server 2012 verfügbar.

Insbesondere bei mehrfacher Ausführung ergeben sich Unterschiede im Laufzeitverhalten, die ich im diesem Beitrag gegenüberstellen möchte. Deshalb habe ich ein kurzes Skript geschrieben, dass diese Unterschiede deutlich machen soll:

set dateformat ymd;
declare @base date = ‚2018-09-11‘;
Set nocount on;
declare @i bigint = 10000000;
declare @x date, @a date;
while @i > 0
begin
    set @x = DATEADD(Day, cast(RAND(@@IDLE)*100000 as int), @base); — 34.12s, 33,85s, 33,88s
´  
–set @a = DateAdd(day, 1-DAY(@x), @x); — 41.28s, 41.97s, 40,87s
–set @a = DateAdd(day, 1, EOMONTH(@x,-1)); — 40.54s, 40,19s, 40.18s
–set @a = cast(cast(DATEPART(month, @x )*100 + DATEPART(year, @x)*10000 + 1 as varchar(8)) as date); — 50.17s, 50.08s, 50.29s
–set @a = cast(SUBSTRING(CONVERT(varchar(12),@x,112),0,7)+ ’01‘ as date); — 50.62s, 51.19s, 50.7s
–set @a = DATEFROMPARTS(year(@x), month(@x), 1); — 45.52s, 45.19s, 45.88s
–set @a = cast(Format(@x,’yyyyMM01′) as date); — 297,01s, 294.84s, 294.22s
    set @i -=1
end

Bei diesem Skript werden 10.000.000 Iterationen über die entsprechende Funktion durchgeführt. Ich habe dabei im ersten Schritt alle Funktion auskommentiert und nacheinander eine nach der anderen wieder aktiv geschalten. Zu jeder dieser Funktion habe ich dreimal dieses Skript ausgeführt und die Ausführungszeit in Sekunden jeweils am Ende der Funktion dokumentiert. Dabei wurden folgende (um die Basiszeit bereinigte) Ergebnisse erreicht:

  1.     6,35s –> DateAdd(day, 1, EOMONTH(@x,-1))
  2.     7,42s –> DateAdd(day, 1-DAY(@x), @x)
  3.   11,58s –> DATEFROMPARTS(year(@x), month(@x), 1)
  4.   16,23s –> cast(cast(DATEPART(month, @x )*100 + DATEPART(year, @x)*10000 + 1 as varchar(8)) as date)
  5.   16,89s –> cast(SUBSTRING(CONVERT(varchar(12),@x,112),0,7)+ ’01‘ as date)
  6. 261,4s  –> cast(Format(@x,’yyyyMM01′) as date)

Zusammenfassung

Wie nicht anders zu erwarten, sind die Statements am schnellsten in der Ausführung, die keine Transformationen in andere Datentypen erfordern und mit wenig Funktionen auskommen.

Für mich etwas überraschend war der Platz 1, dass die EOMonth-Funktion performanter war als die DAY-Funktion von Platz 2.

Die zweite Überraschung war für mich der letzte Platz – keine Ahnung was Microsoft mit der FORMAT-Funktion intern anstellt, um solche Performancewerte zu erhalten. Für mich bedeutet das aber, das ich die Funktion bei Statements über größere Datenmengen vermeiden werden – auch wenn sie vielfältige Funktionen bietet.

Solltet Ihr noch andere Ideen/Vorschläge dafür haben, aus einem vorgegebenen Datum einen Tag  des Monats zu berechnen, dann immer her damit. Ansonsten freue ich mich wie immer über Feedback zu diesem Beitrag.

Das Bild zu diesem Beitrag wurde mit freundlicher Genehmigung von Anka Albrecht (Urheber) zur Verfügung gestellt.

Über

Die IT-Welt wird immer komplexer und zwischen den einzelnen Komponenten gibt es immer mehr Abhängigkeiten. Nachdem ich durch meine tägliche Arbeit immer wieder vor der Herausforderung stehe, komplexe Probleme zu lösen, möchte ich diese Seite dafür verwenden, Euch den einen oder anderen Tipp zu geben, wenn Ihr vor ähnlichen Aufgabenstellungen steht.

Tagged with:
Veröffentlicht in Allgemein, SQL Server, TSQL1

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s

Follow Sylvio's Infobox on WordPress.com
September 2018
M D M D F S S
 12
3456789
10111213141516
17181920212223
24252627282930
%d Bloggern gefällt das: