Podczas pracy z datami w SQL, czasami trzeba obliczyć koniec miesiąca. Miesiące są podstępne! Niektóre mają 28 dni, inne 30 lub 31, a czasami zdarza się rok przestępny!
Więc, biorąc pod uwagę datę, jak obliczyć liczbę dni pozostałych w miesiącu?
Obliczenie jest tak naprawdę procesem dwuetapowym:
- Określ ostatnią datę miesiąca.
- Oblicz różnicę w dniach, pomiędzy datą, o której mowa, a krokiem 1.
Możemy użyć funkcji DATEDIFF do obliczenia różnicy w dniach, ale jak obliczyć ostatni dzień miesiąca?
Using EOMONTH to Calculate the End of the Month
W SQL Server 2012 i nowszych, można użyć funkcji EOMONTH, aby zwrócić ostatni dzień miesiąca.
Na przykład
SELECT EOMONTH('02/04/2016')
Zwraca 02/29/2016
Jak widać, funkcja EOMONTH uwzględnia rok przestępny.
Więc, aby obliczyć liczbę dni od daty do końca miesiąca, mógłbyś napisać
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
Co zwraca 25.
Spróbujmy bardziej kompleksowego przykładu, który oblicza dni pozostałe do końca LoanDate dla bieżącego miesiąca 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
Oto wynik:
Jak widać, DaysRemaining zmienia się w zależności od miesiąca. Zauważ również, że gdy data występuje w tym samym dniu, co koniec miesiąca, np. wiersz 3, pozostaje zero dni.
Spójrzmy teraz, jak można przejść do obliczania tego, jeśli używasz SQL 2008 R2 lub wcześniej.
Old School Method to Calculate Last Day of Month
Obliczenie jest tak naprawdę tym samym dwuetapowym procesem:
- Określ ostatnią datę miesiąca
- Oblicz różnicę w dniach, pomiędzy daną datą a krokiem 1.
Jednakże różnica polega na tym, jak określamy ostatnią datę miesiąca. Ponieważ EOMONTH nie jest dostępny, musimy go obliczyć w „staromodny” sposób. Istnieje kilka metod, aby to zrobić. Oto jedna z nich.
Obliczymy ostatni dzień miesiąca, używając dwóch funkcji: DATEADD i DAY.
Użyjemy DATEADD, aby dodać miesiąc do daty. Następnie funkcją DAY wyznaczymy liczbę dni od początku miesiąca. Odejmując to od daty, którą właśnie obliczyliśmy (tej, która jest o miesiąc do przodu), możemy uzyskać ostatnią datę miesiąca.
OK, wiem, że to brzmi zagmatwanie, więc rozbijmy to używając tego diagramu:
Używając tego samego przykładu, który zrobiliśmy z EOMONTH mamy następujące oświadczenie, które możemy użyć do testowania:
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
Oto wyniki:
Więc, której metody powinieneś użyć?
Jeśli piszę kod i wiem, że będzie działał na SQLServer 2012 lub nowszym, wybrałbym metodę EOMONTH, ponieważ jest o wiele łatwiejsza do odczytania; jednak myślę, że poznanie i zrozumienie metody używając DATEADD i DAY pomoże Ci lepiej zrozumieć, jak manipulować datami.