Při práci s daty SQL někdy potřebujete vypočítat konec měsíce. Měsíce jsou záludné! Některé mají 28 dní, jiné 30 nebo 31, a sem tam se objeví přestupný rok!“
Jak tedy při zadaném datu vypočítat počet dní, které zbývají do konce měsíce?“
Výpočet je vlastně dvoukrokový proces:
- Určete poslední datum měsíce.
- Vypočítejte rozdíl ve dnech, mezi daným datem a krokem 1. V tomto kroku je třeba vypočítat, kolik dní zbývá do konce měsíce.
Pro výpočet rozdílu ve dnech můžeme použít funkci DATEDIFF, ale jak vypočítat poslední den v měsíci?“
Použití funkce EOMONTH pro výpočet konce měsíce
V SQL Serveru 2012 a vyšších verzích můžete pro vrácení posledního dne v měsíci použít funkci EOMONTH.
Například
SELECT EOMONTH('02/04/2016')
Vrátí 02/29/2016
Jak vidíte, funkce EOMONTH bere v úvahu přestupný rok.
Pro výpočet počtu dní od data do konce měsíce byste tedy mohli napsat
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
Což vrátí 25 dní.
Zkusíme komplexnější příklad, který vypočítá počet dní zbývajících do data LoanDate pro aktuální měsíc 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
Tady je výsledek:
Jak vidíte, DaysRemaining se mění podle měsíce. Všimněte si také, že pokud se datum vyskytuje ve stejný den jako konec měsíce, například v řádku 3, zbývá nula dní.
Nyní se podívejme, jak byste postupovali při výpočtu, pokud používáte SQL 2008 R2 nebo starší.
Stará školní metoda výpočtu posledního dne měsíce
Výpočet je vlastně stejný dvoukrokový proces:
- Určit poslední datum měsíce
- Vypočítat rozdíl ve dnech, mezi daným datem a krokem 1.
Rozdíl je však v tom, jak určíme poslední datum měsíce. Protože EOMONTH není k dispozici, musíme jej vypočítat „postaru“. Existuje několik metod, jak to provést. Zde je jeden z nich.
Poslední den měsíce vypočítáme pomocí dvou funkcí: DATEADD a DAY.
Pomocí DATEADD přidáme k datu měsíc. Poté pomocí funkce DAY určíme počet dní od začátku měsíce. Jeho odečtením od data, které jsme právě vypočítali (to, které je o měsíc napřed), získáme poslední datum měsíce.
OK, vím, že to zní zmateně, takže si to rozebereme pomocí tohoto schématu:
Při použití stejného příkladu jako u EOMONTH máme následující příkaz, který můžeme použít k testování:
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
Tady jsou výsledky:
Takže kterou metodu byste měli použít?
Pokud píšu kód a vím, že poběží na SQLServeru 2012 nebo vyšším, volil bych metodu EOMONTH, protože je mnohem přehlednější; nicméně si myslím, že znalost a pochopení metody pomocí DATEADD a DAY vám pomůže lépe porozumět manipulaci s daty.