Pour ceux qui n’ont pas la chance de travailler avec la version 2016, l’utilisation du DATEDIFF peut provoquer une erreur de dépassement de capacité arithmétique du fait que les calculs sont réalisés sur un entier 32 bits (INT) alors que le système effectue un calcul intermédiaire dont la différence à un moment donné peut dépasser la limite du 32 bits (-2 147 483 648 à 2 147 483 647).
Pour contourner ce problème je vous propose l’UDF suivante que j’ai appelé DATE_DIFF…
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
[code language="sql"] CREATE FUNCTION dbo.DATE_DIFF(@UNITE VARCHAR(16), @DT_BEGIN DATETIME2, @DT_END DATETIME2) RETURNS BIGINT AS BEGIN SET @UNITE = LOWER(@UNITE) -- trivial date's differences IF @UNITE = 'year' RETURN CAST(DATEDIFF(year, @DT_BEGIN, @DT_END) AS BIGINT); IF @UNITE = 'quarter' RETURN CAST(DATEDIFF(year, @DT_BEGIN, @DT_END) AS BIGINT); IF @UNITE = 'month' RETURN CAST(DATEDIFF(month, @DT_BEGIN, @DT_END) AS BIGINT); IF @UNITE = 'week' RETURN CAST(DATEDIFF(year, @DT_BEGIN, @DT_END) AS BIGINT); IF @UNITE = 'day' RETURN CAST(DATEDIFF(day, @DT_BEGIN, @DT_END) AS BIGINT); -- for smallest pieces of time use a BIGINT to store DATEDIFF in days DECLARE @DATEDIFF BIGINT = DATEDIFF(day, @DT_BEGIN, @DT_END); DECLARE @h1 BIGINT = DATEPART(hour, @DT_BEGIN), @h2 BIGINT = DATEPART(hour, @DT_END), @m1 BIGINT = DATEPART(minute, @DT_BEGIN), @m2 BIGINT = DATEPART(minute, @DT_END), @s1 BIGINT = DATEPART(second, @DT_BEGIN), @s2 BIGINT = DATEPART(second, @DT_END), @ms1 BIGINT = DATEPART(millisecond, @DT_BEGIN), @ms2 BIGINT = DATEPART(millisecond, @DT_END), @ns1 BIGINT = DATEPART(nanosecond, @DT_BEGIN), @ns2 BIGINT = DATEPART(nanosecond, @DT_END), @µs1 BIGINT = DATEPART(microsecond, @DT_BEGIN), @µs2 BIGINT = DATEPART(microsecond, @DT_END); -- for hour, take @DATEDIFF (days) * 24 + differences between hours IF @UNITE = 'hour' RETURN @DATEDIFF * 24 + @h2 - @h1; -- for minute, take @DATEDIFF (days) * 24 * 60 + difference between hours * 60 + difference between minutes IF @UNITE = 'minute' RETURN @DATEDIFF * 1440 + 60 * (@h2 - @h1) + @m2 - @m1; -- and so on... IF @UNITE = 'second' RETURN @DATEDIFF * 86400 + 3600 * (@h2 - @h1) + 60 * (@m2 - @m1) + @s2 - @s1; IF @UNITE = 'millisecond' RETURN @DATEDIFF * 86400000 + 3600000 * (@h2 - @h1) + 60000 * (@m2 - @m1) + 1000 * (@s2 - @s1) + @ns1 - @ns2; IF @UNITE = 'microsecond' RETURN @DATEDIFF * 86400000000 + 3600000000 * (@h2 - @h1) + 60000000 * (@m2 - @m1) + 1000000 * (@s2 - @s1) + 1000 * (@ns1 - @ns2) + @µs2 - @µs1; RETURN @DATEDIFF; END; GO [/code] |
Exemple d’utilisation :
SELECT dbo.DATE_DIFF('microsecond', '0001-01-01', '9999-12-31')
Le lien de téléchargement du code
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *