Quando si lavora con le date SQL, a volte è necessario calcolare la fine del mese. I mesi sono complicati! Alcuni sono di 28 giorni, altri di 30 o 31, e ogni tanto c’è un anno bisestile!
Quindi, data una data, come si calcola il numero di giorni rimanenti nel mese?
Il calcolo è davvero un processo in due fasi:
- Determinare l’ultima data del mese.
- Calcolare la differenza in giorni, tra la data in questione e il passo 1.
Possiamo usare la funzione DATEDIFF per calcolare la differenza in giorni, ma come si calcola l’ultimo giorno del mese?
Usare EOMONTH per calcolare la fine del mese
In SQL Server 2012 e superiori, potete usare la funzione EOMONTH per restituire l’ultimo giorno del mese.
Per esempio
SELECT EOMONTH('02/04/2016')
Ritorna 02/29/2016
Come potete vedere la funzione EOMONTH tiene conto dell’anno bisestile.
Quindi per calcolare il numero di giorni da una data alla fine del mese si potrebbe scrivere
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
Che restituisce 25.
Proviamo un esempio più completo che calcola i giorni rimanenti su un LoanDate per il mese corrente del 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
Ecco il risultato:
Come potete vedere, il DaysRemaining cambia in base al mese. Inoltre, notate che quando la data si verifica nella stessa data della fine del mese, come la riga 3, rimangono zero giorni.
Ora vediamo come dovreste calcolare questo se state usando SQL 2008 R2 o prima.
Metodo della vecchia scuola per calcolare l’ultimo giorno del mese
Il calcolo è davvero lo stesso processo in due fasi:
- Determinare l’ultima data del mese
- Calcolare la differenza in giorni, tra la data in questione e il passo 1.
Tuttavia, la differenza è nel modo di determinare l’ultima data del mese. Poiché EOMONTH non è disponibile, dobbiamo calcolarlo alla “vecchia maniera”. Ci sono diversi metodi per farlo. Eccone uno.
Calcoleremo l’ultimo giorno del mese usando due funzioni: DATEADD e DAY.
Utilizzeremo DATEADD per aggiungere un mese alla data. Poi la funzione DAY per determinare il numero di giorni dall’inizio del mese. Sottraendo questo alla data che abbiamo appena calcolato (quella che è un mese avanti), possiamo ottenere l’ultima data del mese.
Ok, so che sembra confuso, quindi scomponiamolo usando questo diagramma:
Utilizzando lo stesso esempio che abbiamo fatto con EOMONTH abbiamo la seguente dichiarazione che possiamo usare per testare:
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
Ecco i risultati:
Quindi quale metodo dovresti usare?
Se sto scrivendo del codice e so che verrà eseguito su SQLServer 2012 o superiore, sceglierei il metodo EOMONTH perché è molto più facile da leggere; tuttavia, penso che conoscere e capire il metodo che utilizza DATEADD e DAY aiuti a capire meglio come manipolare le date.