Az SQL dátumokkal való munka során néha ki kell számítani a hónap végét. A hónapok trükkösek! Egyesek 28 naposak, mások 30 vagy 31 naposak, és néha-néha van egy szökőév is!
Hogyan számoljuk ki tehát egy dátum esetén, hogy hány nap van még hátra a hónapból?
A számítás valójában két lépésből áll:
- Meghatározzuk a hónap utolsó dátumát.
- Kiszámítjuk a napok különbségét, a kérdéses dátum és az 1. lépés között.
A DATEDIFF függvénnyel kiszámíthatjuk a napok különbségét, de hogyan számoljuk ki a hónap utolsó napját?
A hónap végének kiszámításához az EOMONTH függvényt használjuk
Az SQL Server 2012 és újabb rendszerekben az EOMONTH függvénnyel visszaadhatjuk a hónap utolsó napját.
Például
SELECT EOMONTH('02/04/2016')
Az eredmény: 2016. 02. 29. 02
Amint látja, az EOMONTH függvény figyelembe veszi a szökőéveket.
Az adott dátumtól a hónap végéig eltelt napok számának kiszámításához tehát azt írhatja
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
, hogy a függvény 25 napot ad vissza.
Kipróbáljunk egy átfogóbb példát, amely kiszámítja a kölcsönDátumból hátralévő napokat a kölcsönDátum aktuális hónapjára:
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
Itt az eredmény:
Mint látható, a DaysRemaining a hónapnak megfelelően változik. Vegyük észre azt is, hogy ha a dátum a hónap végével megegyező dátumra esik, például a 3. sorban, akkor nulla nap marad.
Most nézzük meg, hogyan számolhatná ki ezt, ha az SQL 2008 R2-t vagy azelőttit használja.
Old School Method to Calculate Last Day of the Month
A számítás valójában ugyanaz a kétlépéses folyamat:
- Meghatározzuk a hónap utolsó dátumát
- Kiszámítjuk a napok különbségét, a kérdéses dátum és az 1. lépés között.
A különbség azonban abban van, hogyan határozzuk meg a hónap utolsó dátumát. Mivel az EOMONTH nem áll rendelkezésre, ezért azt a “régimódi” módon kell kiszámítanunk. Erre több módszer is létezik. Íme az egyik.
A hónap utolsó napját két függvény segítségével fogjuk kiszámítani: DATEADD és DAY.
A DATEADD segítségével hozzáadunk egy hónapot a dátumhoz. Ezután a DAY függvényt, hogy meghatározzuk a hónap elejétől számított napok számát. Ha ezt kivonjuk az imént kiszámított dátumból (amelyik egy hónappal előrébb van), megkapjuk a hónap utolsó dátumát.
OK, tudom, hogy ez zavarosan hangzik, ezért bontsuk le a következő ábra segítségével:
Az EOMONTH-hoz hasonló példát használva a következő utasítással tudjuk tesztelni:
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
Itt vannak az eredmények:
Hát melyik módszert használjuk?
Ha kódot írok, és tudom, hogy SQLServer 2012 vagy nagyobb rendszeren fog futni, akkor az EOMONTH módszert választanám, mivel az sokkal könnyebben olvasható; azonban úgy gondolom, hogy a DATEADD és DAY módszer ismerete és megértése segít jobban megérteni a dátumokkal való manipulációt.