Par : Jim Evans | Mis à jour : 2020-07-16 | Commentaires (1) | Connexe : Plus > Dates
Problème
Souvent, nous avons besoin de calculer la différence entre deux dates et de retourner les résultats dans une partie de date ou un incrément souhaité, comme les jours, les heures, les minutes. Heureusement, SQLServer fournit une fonction pour cela.
Dans cet article, je ferai une démonstration des fonctions SQL DATEDIFF et DATEDIFF_BIG et partageraiplusieurs exemples d’utilisation de chacune. Je vous montrerai également les limitations et comment les contourner. Vous apprendrez également comment calculer l’âge de la ville de Saint-Augustin en nanosecondes.
Solution
Nous allons explorer les fonctions DATEDIFF et DATEDIFF_BIG, montrer comment elles sont utilisées, et fournir plusieurs exemples.
Qu’est-ce que la fonction DATEDIFF de SQL Server
DATEDIFF() est une fonction de base de SQL Server qui peut être utilisée pour faire des calculs de date. Plus précisément,elle obtient la différence entre 2 dates avec les résultats retournés dans les unités de date spécifiéescomme années, mois jours, minutes, secondes comme une valeur int (entier).
Syntaxe:
DATEDIFF( DatePart, StartDate, EndDate )
Qu’est-ce que la fonction DATEDIFF_BIG de SQL Server
DATEDIFF_BIG() est une fonction SQL qui a été introduite dans SQL Server 2016. Elle peut être utiliséepour faire des calculs de date également. Plus précisément, elle obtient la différence entre 2 datesavec les résultats retournés en unités de date spécifiées comme années, mois jours, minutes,secondes comme une valeur bigint.
Syntaxe:
DATEDIFF_BIG( DatePart, StartDate, EndDate )
Comment utiliser DATEDIFF et DATEDIFF_BIG
L’utilisation de DATEDIFF et DATEDIFF_BIG est la même : DATEDIFF(datepart of returnvalues, Start Date, End Date).
Exemples:
SELECT DATEDIFF( MILLISECOND, '07-04-2020', '07-05-2020') --> = 86400000 SELECT DATEDIFF_BIG( NANOSECOND, '07-04-2020', '07-05-2020') --> = 86400000000000
Premier paramètre : est unargument de datepart valide qui est l’un des suivants de la nanoseconde à l’année :
| Nom de datepart | Abréviation | année | yy, yyyy |
|---|---|
| quartier | qq, q |
| mois | mm, m |
| jour de l’année | dy, y | jour | dd, d | semaine | wk, ww | Heure | hhh |
| minute | mi, n |
| seconde | ss, s |
| milliseconde | ms | microseconde | mcs | nanoseconde | ns |
Deuxième paramètre : est la date de début. Une variable valide de type date, datetime, datetimeoffset, datetime2, smalldatetime ou heure ou une chaîne de caractères qui s’est résolue en un datatype datetime.
Troisième paramètre : est la date de fin. Une variable de type date valide, datetime, datetimeoffset, datetime2, smalldatetime, ou variable de type données temporelles ou une chaîne de caractères qui s’est résolue en un type de données datetime.
Ces informations peuvent être trouvées sur Microsoft Docs!
SQL Server DATEDIFF Examples
Vous trouverez ci-dessous des exemples de base utilisant les arguments de datapart les plus courants.
| Exemple | Code DATEDIFF du serveur SQL | Sortie |
|---|---|---|
| Combien de minutes dans une journée ? | SELECT DATEDIFF(MINUTE, ’07-04-2020′, ’07-05-2020′) | 1440 minutes |
| Combien d’heures dans une journée ? | SELECT DATEDIFF(HOUR, ’01-01-2020′, ’01-02-2020′) | 24 heures |
| Combien de jours dans une année ? | SELECT DATEDIFF(DAY, ’01-01-2020′, ’12-31-2020′) | 365 jours |
| Combien de mois dans une année ? | SELECT DATEDIFF(MONTH, ’01-01-2019′, ’01-01-2020′) | 12 Mois |
| Combien d’années de 2000 à 2020 ? | SELECT DATEDIFF(YEAR, ’01-01-2000′, ’01-01-2020′) | 20 Années |
Quand utiliser la fonction DATEDIFF_BIG de SQL Server
Utiliser la fonction DATEDIFF_BIG lorsque vos résultats dépassent la plage d’une valeur entière qui est comprise entre (-2,147 483 648 à +2,147 483 647).
BIGINT a une plage de (-9,223,372,036,854,775,808 à 9,223,372,036,854,775,807).Il semble qu’il serait difficile de dépasser cette plage ! Mais nous allons présenter un exemple qui dépasse effectivement cette plage lors de l’utilisation de DATEDIFF_BIG.
SQL Server DATEDIFF_BIG au lieu de DATEDIFF
Comment dépasser la valeur de retour DATEDIFF int ? Microsoft Docs fournit 2 exemplesde dépassement de la valeur de retour entière de DATEDIFF que je démontre ci-dessous et montre comment DATEDIFF_BIG fonctionne pour contourner la limitation.
--1. For millisecond, max difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. -- the following will exceed this range.SELECT DATEDIFF(MILLISECOND, '01-01-2020', '02-01-2020')
Il en résulte l’erreur suivante.
The datediff function resulted in anoverflow. Le nombre de dateparts séparant deux instances de date/heure est toolarge. Essayez d’utiliser datediff avec une partie de date moins précise.
Voici un autre exemple.
--2. For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds. -- the following will exceed this range.SELECT DATEDIFF(SECOND, '01-01-1950', '02-01-2020')
Ceci entraîne l’erreur suivante.
La fonction datediff a entraîné un dépassement. Le nombre de dateparts séparant deux instances de date/heure est toolarge. Essayez d’utiliser datediff avec une partie de date moins précise.
Utiliser plutôt DATEDIFF_BIG pour éviter les erreurs ci-dessus.
--3. How to get around the integer limit using DATEDIFF_BIG functionSELECT DATEDIFF_BIG(MILLISECOND, '01-01-2020', '02-01-2020') --> = 2678400000SELECT DATEDIFF_BIG(SECOND, '01-01-1950', '02-01-2020') –-> = 2211667200
Quand DATEDIFF_BIG n’est pas assez grand
Selon Microsoft Docs, DATEDIFF_BIG ne peut déborder que si l’on utilise la précision nanoseconde où la différence entre enddate et startdate est supérieure à 292 ans,3 mois, 10 jours, 23 heures, 47 minutes et 16.8547758 secondes.
Ceci pourrait être un problème si vous voulez savoir combien de nanosecondes se sont écoulées depuis la fondation de la plus ancienne ville d’Amérique, St. Augustine en Floride !
--1. How many nanoseconds since St. Augustine was founded on Sept. 8, 1565?SELECT DATEDIFF_BIG(NANOSECOND, '09-08-1565', GETDATE())
Dommage ! !!! Nous obtenons une erreur.
La fonction datediff_big a entraîné un débordement. Le nombre de dateparts séparant deux instances de date/heure est toolarge. Essayez d’utiliser datediff_big avec une partie de date moins précise.
Essayons ceci en plusieurs étapes:
--1. First get the number of Years since St. Augustine was foundedSELECT DATEDIFF_BIG(YEAR, '09-08-1565', GETDATE()) --> = 455 --2. Next, get the number of nanoseconds in a yearSELECT DATEDIFF_BIG(NANOSECOND, '01-01-2020 00:00:00.0000000', '12-31-2020 23:59:59.9999999') --> = 31622399999999900--3. Last multiply the results together and cast to decimal(38,0)-- 455 x 31622399999999900 = 14388191999999954500SELECT CAST(455 * 31622399999999900 as DECIMAL(38,0)) --> = 14388191999999954500
Walla ! Nous savons maintenant combien de nanosecondes se sont écoulées depuis la fondation de Saint Augustin.
Wrap Up
J’espère que vous avez apprécié cet exercice sur DATEDIFF et DATEDIFF_BIG. DATEDIFF est une fonction SQL couramment utilisée qui existe depuis de nombreuses années. DATEDIFF_BIG est une fonction relativement nouvelle qui a été introduite dans SQL Server 2016. Bien que j’aie fourni des exemples de débordement de ces fonctions, il est rare que vous rencontriez ces scénarios. Ce sont des fonctions pratiques qui devraient être ajoutées à votre boîte à outils SQL !
Prochaines étapes
- Aller plus en profondeur avec -d’autres articles sur Datediff
- Lire sur -les contournements pour calculer l’âge
- Lire sur d’autres fonctions SQL intéressantes -LAG et LEAD
- Trouver d’autres articles sur Datetime -MS SQL Tips Search on DateDiff
Dernière mise à jour : 2020-07-16
.
A propos de l’auteur
L’auteur de MSSQLTips Jim EvansJim Evans est un responsable informatique actuellement chez Harsco qui a géré des DBA, Développeurs d’applications et de BI et équipes de gestion des données depuis plus de 20 ans.
Voir tous mes conseils
- Plus de conseils pour les développeurs de bases de données…
.