Lorsque vous travaillez avec des dates SQL, vous avez parfois besoin de calculer la fin du mois. Les mois sont délicats ! Certains ont 28 jours, d’autres 30 ou 31, et de temps en temps, il y a une année bissextile !
Donc, étant donné une date, comment calculer le nombre de jours restants dans le mois ?
Le calcul est en fait un processus en deux étapes :
- Déterminer la dernière date du mois.
- Calculer la différence en jours, entre la date en question et l’étape 1.
On peut utiliser la fonction DATEDIFF pour calculer la différence de jours, mais comment calculer le dernier jour du mois ?
Utiliser EOMONTH pour calculer la fin du mois
Dans SQL Server 2012 et plus, vous pouvez utiliser la fonction EOMONTH pour retourner le dernier jour du mois.
Par exemple
SELECT EOMONTH('02/04/2016')
Retourne le 29/02/2016
Comme vous pouvez le voir, la fonction EOMONTH prend en compte l’année bissextile.
Donc, pour calculer le nombre de jours entre une date et la fin du mois, vous pourriez écrire
SELECT DATEDIFF(d,'02/04/2016', EOMONTH('02/04/2016'))
Ce qui retourne 25.
Tentons un exemple plus complet qui calcule les jours restants sur une LoanDate pour le mois en cours de la 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
Voici le résultat :
Comme vous pouvez le voir, le DaysRemaining change en fonction du mois. De plus, remarquez que lorsque la date se produit à la même date que la fin du mois, comme la ligne 3, il reste zéro jour.
Maintenant, regardons comment vous procéderiez pour calculer ceci si vous utilisez SQL 2008 R2 ou avant.
Méthode de la vieille école pour calculer le dernier jour du mois
Le calcul est vraiment le même processus en deux étapes :
- Déterminer la dernière date du mois
- Calculer la différence en jours, entre la date en question et l’étape 1.
Cependant, la différence est dans la façon dont nous déterminons la dernière date du mois. Puisque EOMONTH n’est pas disponible, nous devons le calculer à la « vieille méthode ». Il existe plusieurs méthodes pour le faire. En voici une.
Nous allons calculer le dernier jour du mois en utilisant deux fonctions : DATEADD et DAY.
Nous allons utiliser DATEADD pour ajouter un mois à la date. Puis la fonction DAY pour déterminer le nombre de jours à partir du début du mois. En soustrayant ce nombre à la date que nous venons de calculer (celle qui a un mois d’avance), nous pouvons obtenir la dernière date du mois.
OK, je sais que cela semble confus, alors décomposons-le en utilisant ce diagramme:
En utilisant le même exemple que nous avons fait avec EOMONTH, nous avons l’énoncé suivant que nous pouvons utiliser pour tester:
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
Voici les résultats:
Alors quelle méthode devriez-vous utiliser ?
Si j’écris du code et que je sais qu’il fonctionnera sur SQLServer 2012 ou plus, j’opterais pour la méthode EOMONTH car elle est beaucoup plus facile à lire ; cependant, je pense que le fait de connaître et de comprendre la méthode utilisant DATEADD et DAY vous aide à mieux comprendre comment manipuler les dates.