Bei der Arbeit mit SQL-Daten müssen Sie manchmal das Monatsende berechnen. Monate sind knifflig! Manche haben 28 Tage, andere 30 oder 31, und ab und zu gibt es ein Schaltjahr!
Wie berechnet man also bei einem Datum die Anzahl der verbleibenden Tage im Monat?
Die Berechnung erfolgt in zwei Schritten:
- Bestimmen Sie das letzte Datum des Monats.
- Berechnen Sie die Differenz in Tagen zwischen dem fraglichen Datum und Schritt 1.
Wir können die DATEDIFF-Funktion verwenden, um die Differenz in Tagen zu berechnen, aber wie berechnen Sie den letzten Tag des Monats?
Verwenden von EOMONTH zur Berechnung des Monatsendes
In SQL Server 2012 und höher können Sie die EOMONTH-Funktion verwenden, um den letzten Tag des Monats zurückzugeben.
Zum Beispiel
SELECT EOMONTH('02/04/2016')
Ergibt den 29.02.2016
Wie Sie sehen, berücksichtigt die Funktion EOMONTH das Schaltjahr.
Um also die Anzahl der Tage von einem Datum bis zum Monatsende zu berechnen, könnten Sie schreiben
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
Das ergibt 25.
Lassen Sie uns ein umfassenderes Beispiel versuchen, das die verbleibenden Tage eines LoanDate für den aktuellen Monat des LoanDate berechnet:
BEGIN TRANSACTION-- Sample DataCREATE TABLE LoanDate (LoanID INT, LoanDate DATE);INSERT INTO LoanDate Values (1, '1/1/2016');INSERT INTO LoanDate Values (1, '1/15/2016');INSERT INTO LoanDate Values (1, '1/31/2016');INSERT INTO LoanDate Values (1, '2/15/2016');INSERT INTO LoanDate Values (1, '3/15/2016');INSERT INTO LoanDate Values (1, '4/15/2016');INSERT INTO LoanDate Values (1, '5/15/2016');INSERT INTO LoanDate Values (1, '6/15/2016');INSERT INTO LoanDate Values (1, '7/15/2016');INSERT INTO LoanDate Values (1, '8/15/2016');INSERT INTO LoanDate Values (1, '9/15/2016');INSERT INTO LoanDate Values (1, '10/15/2016');INSERT INTO LoanDate Values (1, '11/15/2016');INSERT INTO LoanDate Values (1, '12/15/2016');-- Select LoanDate, Days in Month, and Days Remaining in MonthSELECT LoanID, LoanDate, EOMONTH(LoanDate) LoanDateEndOfMonth, DATEDIFF(d, LoanDate, EOMONTH(LoanDate)) as DaysRemainingFROM LoanDateROLLBACK
Hier ist das Ergebnis:
Wie Sie sehen können, ändert sich DaysRemaining entsprechend dem Monat. Beachten Sie auch, dass, wenn das Datum auf das gleiche Datum wie das Monatsende fällt, wie z. B. in Zeile 3, null Tage verbleiben.
Schauen wir uns nun an, wie Sie bei der Berechnung vorgehen würden, wenn Sie SQL 2008 R2 oder früher verwenden.
Old School Method to Calculate Last Day of Month
Die Berechnung ist eigentlich derselbe zweistufige Prozess:
- Bestimmen Sie das letzte Datum des Monats
- Berechnen Sie die Differenz in Tagen zwischen dem fraglichen Datum und Schritt 1.
Der Unterschied liegt jedoch darin, wie wir das letzte Datum des Monats bestimmen. Da EOMONTH nicht verfügbar ist, müssen wir es auf „altmodische“ Weise berechnen. Es gibt mehrere Methoden, dies zu tun. Hier ist eine.
Wir werden den letzten Tag des Monats mit zwei Funktionen berechnen: DATEADD und DAY.
Wir verwenden DATEADD, um einen Monat zum Datum hinzuzufügen. Dann die Funktion DAY, um die Anzahl der Tage ab dem Monatsanfang zu bestimmen. Durch Subtraktion dieser Zahl von dem soeben berechneten Datum (das einen Monat voraus ist) erhalten wir das letzte Datum des Monats.
OK, ich weiß, das klingt verwirrend, also lassen Sie es uns anhand dieses Diagramms aufschlüsseln:
Wenn wir das gleiche Beispiel wie bei EOMONTH verwenden, haben wir die folgende Anweisung, die wir zum Testen verwenden können:
BEGIN TRANSACTION-- Sample DataCREATE TABLE LoanDate (LoanID INT, LoanDate DATE);INSERT INTO LoanDate Values (1, '1/1/2016');INSERT INTO LoanDate Values (1, '1/15/2016');INSERT INTO LoanDate Values (1, '1/31/2016');INSERT INTO LoanDate Values (1, '2/15/2016');INSERT INTO LoanDate Values (1, '3/15/2016');INSERT INTO LoanDate Values (1, '4/15/2016');INSERT INTO LoanDate Values (1, '5/15/2016');INSERT INTO LoanDate Values (1, '6/15/2016');INSERT INTO LoanDate Values (1, '7/15/2016');INSERT INTO LoanDate Values (1, '8/15/2016');INSERT INTO LoanDate Values (1, '9/15/2016');INSERT INTO LoanDate Values (1, '10/15/2016');INSERT INTO LoanDate Values (1, '11/15/2016');INSERT INTO LoanDate Values (1, '12/15/2016');-- Select LoanDate, Days in Month, and Days Remaining in MonthSELECT LoanID, LoanDate, DATEADD(dd,-(DAY(DATEADD(mm,1,LoanDate))), DATEADD(mm,1,LoanDate)) LoanDateEndOfMonth, DATEDIFF(d,LoanDate, DATEADD(dd,-(DAY(DATEADD(mm,1,LoanDate))), DATEADD(mm,1,LoanDate))) as DaysRemainingFROM LoanDateROLLBACK
Hier sind die Ergebnisse:
Welche Methode sollten Sie also verwenden?
Wenn ich Code schreibe, von dem ich weiß, dass er auf SQLServer 2012 oder höher läuft, würde ich die EOMONTH-Methode wählen, da sie viel einfacher zu lesen ist; ich denke jedoch, dass das Wissen und Verständnis der Methode mit DATEADD und DAY Ihnen hilft, besser zu verstehen, wie man Daten manipuliert.