Clevere Datums-Tricks mit SQL (TSQL, MySQL)

Wenn man mit SQL den letzten Tag des aktuellen Monats oder den ersten Tag der letzten Woche ausgeben möchte, steht man vor einer Herausforderung. Die verschiedenen Datenbanksysteme wie MySQL, Microsoft SQL Server oder andere bietet dafür oft keine Standardfunkionen. Hierzu ist es notwendig mehrere Funktionen zu kombinieren.

Die Herausforderung

Die gute Nachricht zuerst: Es ist problemlos möglich, verschiedene Datums-Tricks mit SQL anzuwenden und der Weg ist in allen Datenbanksystemen sehr ähnlich. Lediglich die aufzurufenden Funktionen unterscheiden sich.

Mal angenommen, Sie möchten mit SQL einige Datumsangaben automatisiert ausgeben. Wenn man zum Beispiel den letzten Tag des Monats Februar erhalten möchte, der sich aber ausgerechnet in einem Schaltjahr (wie 2016) befindet, steht man erst einmal vor der Frage, wie man das korrekt ermittelt. Hierzu habe ich einige solcher häufig benötigter Datums-Angaben zusammengestellt und in SQL formuliert.

Funktioniert immer – auch bei Schaltjahren

Diese Code-Schnipsel sind darauf ausgelegt, mit Hilfe der Datenbanksysteme immer das aktuelle Datum auszugeben. Das gilt natürlich auch für Schaltjahre.

Die Funktionsweise von Datums-Angaben mit SQL

Anhand einiger Beispiele möchte ich die Funktionsweise der unten stehenden Skripte erläutern.

Aktuelles Datum mit SQL

Zuerst wird immer das aktuelle Datum ermittelt. Dazu gibt es in den unterschiedlichen SQL-Dialekten unterschiedliche Befehle. Bei einigen funktioniert CURRENT DATE (oder CURRENT_DATE mit Unterstrich), bei anderen wird mit der Funktion NOW() oder es wird mittels CURRENT_TIMESTAMP gearbeitet.

Erster Tag des aktuellen Monats

Nachdem das Bezugsdatum, also das aktuelle Datum, ermittelt wurde, wird nach ständigen Konstanten gesucht. Beim ersten Tag des Monats ist es immer Tag 1. Zusätzlich wird der Monat und das Jahr aus dem aktuellen Datum ermittelt. Diese Einzelteile des Datums werden am Ende mit Hilfe einer passenden Funktion wieder zu einer Ausgabe mit dem Datums-Datentyp konvertiert.

Letzter Tag des aktuellen Monats

Bei der Ermittlung des letzten Tag des Monats wird es ein wenig komplizierter. Zuerst nimmt man an, es müsste geprüft werden, um welchen Monat es sich handelt und dann entweder den Tag 30 oder 31 einsetzen. Doch was ist im Februar? Das ist einfach, der Februar hat 28 Tage… aber Moment… was ist mit Schaltjahren? Dann hat der Februar 29 Tage.

Hier bedienen wir uns eines einfachen Tricks, der auch mit Programmiersprachen möglich ist. Die SQL-Dialekte beinhalten meistens Datums-Funktionen, die das Rechnen ermöglichen. So können wir zum Beispiel Tage addieren oder subtrahieren.

Am Beispiel des 17.02.2016 gilt also folgender Ablauf:

1. Aktuelles Datum ermitteln: 17.02.2016
2. Auf den ersten Tag des Monats kürzen: 01.02.2016
3. Einen Monat addieren: 01.03.2016
4. Einen Tag subtrahieren: 29.02.2016

Dieses Wissen kann man dann auch auf die anderen Szenarien, wie erster Tag des letzten Monats, letzter Tag des nächsten Jahres usw. anwenden.

Ende der letzten Woche mit SQL

Bei den Wochen muss noch eine weitere Komponente berücksichtigt werden, damit der erste Tag der Woche und der letzte Tag der Woche korrekt ermittelt werden. Mit Hilfe einer Funktion, die den numerischen Tag der Woche ausgibt, kann dieses Problem gelöst werden.

Zum Beispiel ist Montag der 1. Tag und Sonntag der 7. Tag.

Hier unterscheiden sich die Datenbanksysteme am meisten voneinander. Einige geben als 1. Tag den Sonntag an, manche arbeiten mit einem Index, beginnen also bei 0. Andere verwenden die Datums-Einstellungen des Computers, die den 1. Tag auf Sonntag oder Montag festlegen. Hier muss im Zweifelsfall getestet werden, ob die berechneten Werte stimmen.

Es werden die Funktionen WEEKDAY(...) (MySQL), DATEPART(WEEKDAY, ...) (TSQL, Microsoft SQL Server) oder DOW(...) (Sybase SQL Anywhere) verwendet.

Kompatibilität zwischen den SQL-Dialekten

Leider sind die Datums-Funktionen nicht im SQL-92-Standard definiert, der eine sehr große Kompatibilität sicherstellt.

Daher hat jeder Hersteller seine eigene Implementierung erstellt, die sich durch die Funktionsaufrufe der Datums-Berechnungen unterscheiden und zusätzlich bei der Berechnung der Wochentage.

Datums-Berechnungen mit SQL

• MySQL: DATE_ADD(CURRENT_DATE, INTERVAL -1 DAY)
• TSQL: DATEADD(DAY, -1, CURRENT_TIMESTAMP)
• Sybase SA: DATEADD(DAY, -1, CURRENT DATE)

Numerischen Wochentag mit SQL berechnen

• MySQL: WEEKDAY(CURRENT_DATE)
• TSQL: DATEPART(WEEKDAY, CURRENT_TIMESTAMP)
• Sybase SA: DOW(CURRENT DATE)

Code für MySQL

-- current date
SELECT CURRENT_DATE AS date_current_date;

-- start of last week (begin: monday)
SELECT DATE_ADD(DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE) DAY), INTERVAL -1 WEEK) AS start_of_last_week;

-- end of last week (begin: monday)
SELECT DATE_ADD(DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE)+6 DAY), INTERVAL -1 WEEK) AS end_of_last_week;

-- start of current week (begin: monday)
SELECT DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE) DAY) AS start_of_current_week;

-- end of current week (begin: monday)
SELECT DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE)+6 DAY) AS end_of_current_week;

-- start of next week (begin: monday)
SELECT DATE_ADD(DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE) DAY), INTERVAL 1 WEEK) AS start_of_next_week;

-- end of next week (begin: monday)
SELECT DATE_ADD(DATE_ADD(CURRENT_DATE, INTERVAL -WEEKDAY(CURRENT_DATE)+6 DAY), INTERVAL 1 WEEK) AS end_of_next_week;

-- start of last month
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE)-2 MONTH) AS start_of_last_month;

-- end of last month
SELECT DATE_ADD(DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE)-1 MONTH), INTERVAL -1 DAY) AS end_of_last_month;

-- start of current month
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE)-1 MONTH) AS start_of_current_month;

-- end of current month
SELECT DATE_ADD(DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE) MONTH), INTERVAL -1 DAY) AS end_of_current_month;

-- start of next month
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE) MONTH) AS start_of_next_month;

-- end of next month
SELECT DATE_ADD(DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL MONTH(CURRENT_DATE)+1 MONTH), INTERVAL -1 DAY) AS end_of_next_month;

-- start of last year
SELECT MAKEDATE(YEAR(CURRENT_DATE)-1, 1) AS start_of_last_year;

-- end of last year
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE), 1), INTERVAL -1 DAY) AS end_of_last_year;

-- start of current year
SELECT MAKEDATE(YEAR(CURRENT_DATE), 1) AS start_of_current_year;

-- end of current year
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE)+1, 1), INTERVAL -1 DAY) AS end_of_current_year;

-- start of next year
SELECT MAKEDATE(YEAR(CURRENT_DATE)+1, 1) AS start_of_next_year;

-- end of next year
SELECT DATE_ADD(MAKEDATE(YEAR(CURRENT_DATE)+2, 1), INTERVAL -1 DAY) AS end_of_next_year;
  

Code für SQL Server (TSQL)

-- current date
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS date_current_date

-- start of last week (begin: monday)
SELECT CAST(DATEADD(WEEK, -1 ,DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 1, CURRENT_TIMESTAMP)) AS DATE) AS start_of_last_week

-- end of last week (begin: monday)
SELECT CAST(DATEADD(WEEK, -1, DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 7, CURRENT_TIMESTAMP)) AS DATE) AS end_of_last_week

-- start of current week (begin: monday)
SELECT CAST(DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 1, CURRENT_TIMESTAMP) AS DATE) AS start_of_current_week

-- end of current week (begins monday)
SELECT CAST(DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 7, CURRENT_TIMESTAMP) AS DATE) AS end_of_current_week

-- start of next week (begin: monday)
SELECT CAST(DATEADD(WEEK, 1 ,DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 1, CURRENT_TIMESTAMP)) AS DATE) AS start_of_next_week

-- end of next week (begin: monday)
SELECT CAST(DATEADD(WEEK, 1, DATEADD(DAY, -DATEPART(WEEKDAY, CURRENT_TIMESTAMP) + 7, CURRENT_TIMESTAMP)) AS DATE) AS end_of_next_week

-- start of last month
SELECT CAST(DATEADD(MONTH, -1 , DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1)) AS DATE) AS start_of_last_month

-- end of last month
SELECT CAST(DATEADD(DAY, -1 , DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1)) AS DATE) AS end_of_last_month

-- start of current month
SELECT DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1) AS start_of_current_month

-- end of current month
SELECT DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP),1))) AS end_of_current_month

-- start of next month
SELECT DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP), 1)) AS start_of_next_month

-- end of next month
SELECT DATEADD(DAY, -1, DATEADD(MONTH, 2, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP),MONTH(CURRENT_TIMESTAMP), 1))) AS end_of_next_month

-- start of last year
SELECT DATEADD(YEAR, -1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)) AS start_of_last_year

-- end of last year
SELECT DATEADD(DAY, -1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)) AS end_of_last_year

-- start of current year
SELECT DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1) AS start_of_current_year

-- end of current year
SELECT DATEADD(DAY, -1, DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1))) AS start_of_current_year

-- start of next year
SELECT DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1)) AS start_of_next_year

-- end of next year
SELECT DATEADD(DAY, -1, DATEADD(YEAR, 2, DATEFROMPARTS(YEAR(CURRENT_TIMESTAMP), 1, 1))) AS end_of_next_year
  

Code für Sybase SQL Anywhere

-- current date
SELECT CURRENT DATE AS date_current_date

-- start of last week (begin: monday)
SELECT CAST(DATEADD(WEEK, -1, DATEADD(DAY, -DOW(CURRENT DATE)+2, CURRENT DATE)) AS DATE) AS start_of_last_week

-- end of last week (begin: monday)
SELECT CAST(DATEADD(WEEK, -1, DATEADD(DAY, -DOW(CURRENT DATE)+8, CURRENT DATE)) AS DATE) AS end_of_last_week

-- start of current week (begin: monday)
SELECT CAST(DATEADD(DAY, -DOW(CURRENT DATE)+2, CURRENT DATE) AS DATE) AS start_of_current_week

-- end of current week (begin: monday)
SELECT CAST(DATEADD(DAY, -DOW(CURRENT DATE)+8, CURRENT DATE) AS DATE) AS end_of_current_week

-- start of next week (begin: monday)
SELECT CAST(DATEADD(WEEK, 1, DATEADD(DAY, -DOW(CURRENT DATE)+2, CURRENT DATE)) AS DATE) AS start_of_next_week

-- end of next week (begin: monday)
SELECT CAST(DATEADD(WEEK, 1, DATEADD(DAY, -DOW(CURRENT DATE)+8, CURRENT DATE)) AS DATE) AS end_of_next_week

-- start of last month
SELECT CAST(DATEADD(MONTH, -1, YMD(YEAR(CURRENT DATE), MONTH(CURRENT DATE), 1)) AS DATE) AS start_as_last_month

-- end of last month
SELECT CAST(DATEADD(DAY, -1, YMD(YEAR(CURRENT DATE), MONTH(CURRENT DATE), 1)) AS DATE) AS end_of_last_month

-- start of current month
SELECT YMD(YEAR(CURRENT DATE), MONTH(CURRENT DATE), 1) AS start_of_current_month

-- end of current month
SELECT CAST(DATEADD(MONTH, 1, YMD(YEAR(CURRENT DATE), MONTH(CURRENT DATE), 1)) AS DATE) AS end_of_current_month

-- start of next month
SELECT CAST(DATEADD(MONTH, 1, YMD(YEAR(CURRENT DATE), MONTH(CURRENT DATE), 1)) AS DATE) AS start_of_next_month

-- end of next month
SELECT CAST(DATEADD(DAY, -1, DATEADD(MONTH, 2 ,YMD(YEAR(CURRENT DATE), MONTH(CURRENT DATE), 1))) AS DATE) AS end_of_next_month

-- start of last year
SELECT CAST(DATEADD(YEAR, -1, YMD(YEAR(CURRENT DATE), 1, 1)) AS DATE) AS start_of_last_year

-- end of last year
SELECT CAST(DATEADD(DAY, -1, YMD(YEAR(CURRENT DATE), 1, 1)) AS DATE) AS end_of_last_year

-- start of current year
SELECT YMD(YEAR(CURRENT DATE), 1, 1) AS start_of_current_year

-- end of current year
SELECT CAST(DATEADD(DAY, -1, DATEADD(YEAR, 1, YMD(YEAR(CURRENT DATE), 1, 1))) AS DATE) AS end_of_current_year

-- start of next year
SELECT CAST(DATEADD(YEAR, 1, YMD(YEAR(CURRENT DATE), 1, 1)) AS DATE) AS start_of_next_year

-- end of next year
SELECT CAST(DATEADD(DAY, -1, DATEADD(YEAR, 2, YMD(YEAR(CURRENT DATE), 1, 1))) AS DATE) AS end_of_next_year