När du arbetar med SQL-datum behöver du ibland beräkna slutet av månaden. Månader är knepiga! Vissa är 28 dagar, andra 30 eller 31, och då och då finns det ett skottår!
Så, givet ett datum, hur beräknar du antalet dagar som återstår i månaden?
Beräkningen är egentligen en tvåstegsprocess:
- Bestäm det sista datumet i månaden.
- Beräkna skillnaden i dagar, mellan datumet i fråga och steg 1.
Vi kan använda funktionen DATEDIFF för att beräkna skillnaden i dagar, men hur beräknar man månadens sista dag?
Användning av EOMONTH för att beräkna månadens slut
I SQL Server 2012 och senare kan du använda funktionen EOMONTH för att returnera månadens sista dag.
Till exempel
SELECT EOMONTH('02/04/2016')
Returnerar 02/29/2016
Som du kan se tar EOMONTH-funktionen hänsyn till skottår.
Så för att beräkna antalet dagar från ett datum till månadens slut kan du skriva
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
som returnerar 25.
Låt oss prova ett mer omfattande exempel som beräknar antalet dagar som återstår på ett LoanDate för LoanDates aktuella månad:
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
Här är resultatet:
Som du kan se ändras DaysRemaining beroende på månad. Lägg också märke till att när datumet inträffar på samma datum som slutet av månaden, t.ex. rad 3, återstår noll dagar.
Nu ska vi titta på hur du skulle gå tillväga för att beräkna detta om du använder SQL 2008 R2 eller tidigare.
Old school method to calculate last day of month
Beräkningen är egentligen samma tvåstegsprocess:
- Bestäm det sista datumet i månaden
- Beräkna skillnaden i dagar, mellan datumet i fråga och steg 1.
Den stora skillnaden ligger dock i hur vi bestämmer det sista datumet i månaden. Eftersom EOMONTH inte finns tillgängligt måste vi beräkna det på det ”gammaldags” sättet. Det finns flera metoder för att göra detta. Här är en.
Vi ska beräkna månadens sista dag med hjälp av två funktioner: DATEADD och DAY.
Vi använder DATEADD för att lägga till en månad till datumet. Sedan funktionen DAY för att bestämma antalet dagar från månadens början. Genom att subtrahera detta från det datum vi just beräknade (det som ligger en månad framåt) kan vi få fram månadens sista datum.
OK, jag vet att det låter förvirrande, så låt oss dela upp det med hjälp av det här diagrammet:
Med samma exempel som vi gjorde med EOMONTH har vi följande uttalande som vi kan använda för att testa:
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
Här är resultaten:
Så vilken metod ska du använda?
Om jag skriver kod som jag vet kommer att köras på SQLServer 2012 eller senare, skulle jag välja EOMONTH-metoden eftersom den är mycket lättare att läsa, men jag tror att om man känner till och förstår metoden med DATEADD och DAY så hjälper det en att bättre förstå hur man manipulerar datum.