Työskennellessäsi SQL-päivämäärien kanssa joudut joskus laskemaan kuukauden lopun. Kuukaudet ovat hankalia! Joissakin on 28 päivää, toisissa 30 tai 31 päivää, ja silloin tällöin on karkausvuosi!
Miten siis lasketaan päivämäärän perusteella, kuinka monta päivää kuukaudesta on jäljellä?
Laskenta on oikeastaan kaksivaiheinen prosessi:
- Määritä kuukauden viimeinen päivä.
- Lasketaan päivien erotus kyseisen päivämäärän ja askeleen 1 välillä.
Voidaan käyttää DATEDIFF-funktiota päivien eron laskemiseen, mutta miten lasketaan kuukauden viimeinen päivä?
Kuukauden viimeisen päivän laskeminen EOMONTH-funktion avulla
SQL Server 2012:ssa ja sitä uudemmissa SQL Server 2012:ssa voit käyttää EOMONTH-funktiota kuukauden viimeisen päivän palauttamiseen.
Esimerkiksi
SELECT EOMONTH('02/04/2016')
Palauttaa 29.02.2016
Kuten näet, EOMONTH-funktio ottaa huomioon karkausvuoden.
Lasketaksesi siis päivämäärän päivien lukumäärän päivämäärän ja kuukauden lopun väliltä voit kirjoittaa
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
Tuloksena on 25.
Kokeillaan kattavampaa esimerkkiä, joka laskee LainaPäivämäärän jäljellä olevat päivät LainaPäivämäärän kuluvalle kuukaudelle:
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
Tässä on tulos:
Kuten huomaat, DaysRemaining muuttuu kuukauden mukaan. Huomaa myös, että kun päivämäärä osuu samaan päivämäärään kuin kuukauden loppu, kuten rivillä 3, jäljellä on nolla päivää.
Katsotaan nyt, miten tämä laskettaisiin, jos käytät SQL 2008 R2:ta tai sitä vanhempaa.
Vanhan koulukunnan menetelmä kuukauden viimeisen päivän laskemiseen
Laskenta on oikeastaan sama kaksivaiheinen prosessi:
- Määritä kuukauden viimeinen päivä
- Lasketaan päivien erotus, kyseisen päivämäärän ja vaiheen 1 välillä.
Ero on kuitenkin siinä, miten määritämme kuukauden viimeisen päivän. Koska EOMONTH ei ole käytettävissä, meidän on laskettava se ”vanhanaikaisella” tavalla. Tähän on olemassa useita menetelmiä. Tässä on yksi.
Laskemme kuukauden viimeisen päivän käyttämällä kahta funktiota: DATEADD ja DAY.
Käytämme DATEADD:tä lisätäksemme kuukauden päivämäärään. Sitten DAY-funktiota määrittääksemme päivien määrän kuukauden alusta. Vähentämällä tämän juuri laskemastamme päivämäärästä (joka on kuukauden edellä) saamme kuukauden viimeisen päivämäärän.
OK, tiedän, että tämä kuulostaa hämmentävältä, joten puretaan se tämän kaavion avulla:
Käyttämällä samaa esimerkkiä kuin EOMONTH:n kohdalla saamme seuraavan lausekkeen, jota voimme käyttää testaamiseen:
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
Tässä ovat tulokset:
Kumpaa metodia siis kannattaa käyttää?
Jos kirjoitan koodia ja tiedän, että se toimii SQLServer 2012:lla tai suuremmalla, valitsisin EOMONTH-menetelmän, koska se on paljon helpompi lukea; mielestäni DATEADD- ja DAY-menetelmän tunteminen ja ymmärtäminen auttavat kuitenkin ymmärtämään paremmin, miten päivämääriä käsitellään.