Când lucrați cu date SQL, uneori trebuie să calculați sfârșitul lunii. Lunile sunt înșelătoare! Unele au 28 de zile, altele 30 sau 31, iar din când în când există un an bisect!
Atunci, dată fiind o dată, cum calculați numărul de zile rămase din lună?
Calculul este de fapt un proces în doi pași:
- Determinați ultima dată a lunii.
- Calculați diferența în zile, între data în cauză și pasul 1.
Puteți utiliza funcția DATEDIFF pentru a calcula diferența de zile, dar cum se calculează ultima zi a lunii?
Utilizarea funcției EOMONTH pentru a calcula sfârșitul lunii
În SQL Server 2012 și versiunile ulterioare, puteți utiliza funcția EOMONTH pentru a returna ultima zi a lunii.
De exemplu
SELECT EOMONTH('02/04/2016')
Întoarce 02/29/2016
După cum puteți vedea, funcția EOMONTH ia în considerare anul bisect.
Așa că pentru a calcula numărul de zile de la o dată până la sfârșitul lunii ați putea scrie
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
Ceea ce returnează 25.
Să încercăm un exemplu mai cuprinzător care calculează numărul de zile rămase la o dată de împrumut (LoanDate) pentru luna curentă a LoanDatei:
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
Iată rezultatul:
Cum puteți vedea, DaysRemaining se schimbă în funcție de lună. De asemenea, observați că atunci când data apare la aceeași dată cu sfârșitul lunii, cum ar fi rândul 3, rămân zero zile.
Acum să vedem cum ați putea calcula acest lucru dacă utilizați SQL 2008 R2 sau o versiune anterioară.
Metoda veche a școlii pentru a calcula ultima zi a lunii
Calculul este, de fapt, același proces în doi pași:
- Determinați ultima dată a lunii
- Calculați diferența în zile, între data în cauză și pasul 1.
Cu toate acestea, diferența constă în modul în care determinăm ultima dată a lunii. Deoarece EOMONTH nu este disponibil, trebuie să o calculăm în modul „de modă veche”. Există mai multe metode pentru a face acest lucru. Iată una dintre ele.
Vom calcula ultima zi a lunii folosind două funcții: DATEADD și DAY.
Vom folosi DATEADD pentru a adăuga o lună la dată. Apoi funcția DAY pentru a determina numărul de zile de la începutul lunii. Prin scăderea acestui număr din data pe care tocmai am calculat-o (cea care este cu o lună înainte), putem obține ultima dată a lunii.
OK, știu că sună confuz, așa că haideți să o descompunem folosind această diagramă:
Utilizând același exemplu ca și în cazul EOMONTH avem următoarea instrucțiune pe care o putem folosi pentru a 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
Iată rezultatele:
Atunci ce metodă ar trebui să folosiți?
Dacă scriu cod și știu că va rula pe SQLServer 2012 sau mai mare, aș alege metoda EOMONTH deoarece este mult mai ușor de citit; totuși, cred că știind și înțelegând metoda care folosește DATEADD și DAY vă ajută să înțelegeți mai bine cum să manipulați datele.
.