Når du arbejder med SQL-datoer, har du nogle gange brug for at beregne slutningen af måneden. Måneder er vanskelige! Nogle er 28 dage, andre 30 eller 31, og af og til er der et skudår!
Så, når man har en dato, hvordan beregner man så antallet af dage, der er tilbage i måneden?
Beregningen er i virkeligheden en proces i to trin:
- Bestem den sidste dato i måneden.
- Beregne forskellen i dage, mellem den pågældende dato og trin 1.
Vi kan bruge funktionen DATEDIFF til at beregne forskellen i dage, men hvordan beregner du sidste dag i måneden?
Anvendelse af EOMONTH til at beregne slutningen af måneden
I SQL Server 2012 og nyere kan du bruge funktionen EOMONTH til at returnere den sidste dag i måneden.
For eksempel
SELECT EOMONTH('02/04/2016')
Returnerer 29/02/2016
Som du kan se, tager EOMONTH-funktionen hensyn til skudår.
Så for at beregne antallet af dage fra en dato til slutningen af måneden kan du skrive
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
Hvilket returnerer 25.
Lad os prøve et mere omfattende eksempel, der beregner de resterende dage på en LoanDate for LoanDates aktuelle måned:
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
Her er resultatet:
Som du kan se, ændres DaysRemaining alt efter måneden. Bemærk også, at når datoen forekommer på samme dato som slutningen af måneden, f.eks. i række 3, er der nul dage tilbage.
Lad os nu se på, hvordan du ville gå til at beregne dette, hvis du bruger SQL 2008 R2 eller tidligere.
Gammel skolemetode til at beregne sidste dag i måneden
Beregningen er i virkeligheden den samme proces i to trin:
- Bestem den sidste dato i måneden
- Beregne forskellen i dage, mellem den pågældende dato og trin 1.
Den forskel ligger dog i, hvordan vi bestemmer den sidste dato i måneden. Da EOMONTH ikke er tilgængelig, er vi nødt til at beregne den på den “gammeldags” måde. Der findes flere metoder til at gøre dette. Her er en.
Vi beregner den sidste dag i måneden ved hjælp af to funktioner: DATEADD og DAY.
Vi skal bruge DATEADD til at tilføje en måned til datoen. Derefter funktionen DAY til at bestemme antallet af dage fra begyndelsen af måneden. Ved at trække dette fra den dato, vi lige har beregnet (den dato, der ligger en måned frem), kan vi få den sidste dato i måneden.
OK, jeg ved godt, at det lyder forvirrende, så lad os bryde det ned ved hjælp af dette diagram:
Med det samme eksempel, som vi gjorde med EOMONTH, har vi følgende erklæring, som vi kan bruge til at teste:
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
Her er resultaterne:
Så hvilken metode skal du bruge?
Hvis jeg skriver kode, og jeg ved, at den vil køre på SQLServer 2012 eller højere, ville jeg vælge EOMONTH-metoden, da den er meget nemmere at læse; jeg tror dog, at det at kende og forstå metoden ved hjælp af DATEADD og DAY hjælper dig til bedre at forstå, hvordan man manipulerer datoer.