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.

Kommentar im Code	Beschreibung	Bezugsdatum	Berechnetes Datum
`-- current date`	Aktuelles Datum	17.03.2016	17.03.2015
Woche
`-- start of last week (begin: monday)`	Beginn der letzten Woche	17.03.2016	07.03.2016
`-- end of last week (begin: monday)`	Ende der letzten Woche	17.03.2016	13.03.2016
`-- start of current week (begin: monday)`	Beginn der aktuellen Woche	17.03.2016	14.03.2016
`-- end of current week (begin: monday)`	Ende der aktuellen Woche	17.03.2016	20.03.2016
`-- start of next week (begin: monday)`	Beginn der nächsten Woche	17.03.2016	21.03.2016
`-- end of next week (begin: monday)`	Ende der nächsten Woche	17.03.2016	27.03.2016
Monat
`-- start of last month`	Beginn des letzten Monats	17.03.2016	01.02.2016
`-- end of last month`	Ende des letzten Monats	17.03.2016	29.02.2016
`-- start of current month`	Beginn des aktuellen Monats	17.03.2016	01.03.2016
`-- end of current month`	ende des aktuellen Monats	17.03.2016	31.03.2016
`-- start of next month`	Beginn des nächsten Monats	17.03.2016	01.04.2016
`-- end of next month`	Ende des nächsten Monats	17.03.2016	30.04.2016
Jahr
`-- start of last year`	Beginn des letzten Jahres	17.03.2016	01.01.2015
`-- end of last year`	Ende des letzten Jahres	17.03.2016	31.12.2015
`-- start of current year`	Beginn des aktuellen Jahres	17.03.2016	01.01.2016
`-- end of current year`	Ende des aktuellen Jahres	17.03.2016	31.12.2016
`-- start of next year`	Beginn des nächsten Jahres	17.03.2016	01.01.2017
`-- end of next year`	Ende des nächsten Jahres	17.03.2016	31.01.2017

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:

Aktuelles Datum ermitteln: 17.02.2016
Auf den ersten Tag des Monats kürzen: 01.02.2016
Einen Monat addieren: 01.03.2016
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