Wanneer u met SQL-data werkt, moet u soms het einde van de maand berekenen. Maanden zijn lastig! Sommige zijn 28 dagen, andere 30 of 31, en af en toe is er een schrikkeljaar!
Dus, gegeven een datum, hoe bereken je het aantal resterende dagen in de maand?
De berekening is eigenlijk een proces in twee stappen:
- Bepaal de laatste datum van de maand.
- Bereken het verschil in dagen tussen de datum in kwestie en de eerste stap.
We kunnen de functie DATEDIFF gebruiken om het verschil in dagen te berekenen, maar hoe bereken je de laatste dag van de maand?
EOMONTH gebruiken om het einde van de maand te berekenen
In SQL Server 2012 en hoger kunt u de functie EOMONTH gebruiken om de laatste dag van de maand terug te geven.
Voorbeeld
SELECT EOMONTH('02/04/2016')
Returns 02/29/2016
Zoals u kunt zien houdt de EOMONTH functie rekening met schrikkeljaren.
Om het aantal dagen vanaf een datum tot het einde van de maand te berekenen zou u dus kunnen schrijven
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
Wat 25 retourneert.
Laten we eens een uitgebreider voorbeeld proberen dat het aantal resterende dagen voor een LoanDate berekent voor de huidige maand van de LoanDate:
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 is het resultaat:
Zoals u kunt zien, verandert de DaysRemaining afhankelijk van de maand. Merk ook op dat wanneer de datum op dezelfde datum valt als het einde van de maand, zoals in rij 3, er nul dagen overblijven.
Laten we nu eens kijken hoe u dit zou moeten berekenen als u SQL 2008 R2 of eerder gebruikt.
Old School Methode om Laatste Dag van Maand te Berekenen
De berekening is eigenlijk hetzelfde proces in twee stappen:
- Bepaal de laatste datum van de maand
- Bereken het verschil in dagen, tussen de datum in kwestie en de stap 1.
Het verschil zit hem echter in de manier waarop we de laatste datum van de maand bepalen. Omdat EOMONTH niet beschikbaar is, moeten we het op de “ouderwetse” manier berekenen. Er zijn verschillende methoden om dit te doen. Hier is er een.
We berekenen de laatste dag van de maand met twee functies: DATEADD en DAY.
We gebruiken DATEADD om een maand aan de datum toe te voegen. Dan de functie DAY om het aantal dagen vanaf het begin van de maand te bepalen. Door dit af te trekken van de datum die we zojuist hebben berekend (de datum die een maand vooruit ligt), kunnen we de laatste datum van de maand krijgen.
OK, ik weet dat dit verwarrend klinkt, dus laten we het met behulp van dit diagram uitsplitsen:
Gebruik makend van hetzelfde voorbeeld als we met EOMONTH hebben gedaan, hebben we het volgende statement dat we kunnen gebruiken om te testen:
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 zijn de resultaten:
Dus welke methode moet je gebruiken?
Als ik code schrijf waarvan ik weet dat die op SQLServer 2012 of hoger draait, zou ik voor de EOMONTH-methode gaan omdat die veel gemakkelijker te lezen is; ik denk echter dat het kennen en begrijpen van de methode met DATEADD en DAY je helpt beter te begrijpen hoe je datums manipuleert.