DATEDIFF error message 535, comment le contourner !

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…

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

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 * * * * *

Ce contenu a été publié dans scripts SQL, SQL Server, Tips and tricks, avec comme mot(s)-clé(s) , , , , . Vous pouvez le mettre en favoris avec ce permalien.