SQL : Utilisation de DATEADD avec bigints

Question

J'ai un SQL pour convertir les dates javascript en dates SQL qui fonctionne très bien. Cependant, j'ai encoutré quelques données qui sont trop grandes et qui causent une exception :

Erreur de dépassement arithmétique lors de la conversion d'une expression en données de type int

Voici le SQL en question :

  DATEADD(MILLISECOND, cast(569337307200000 as bigint) % 1000, DATEADD(SECOND, cast(569337307200000 as bigint) / 1000, '19700101'))

J'utilise SQL Server 2008.

Answer 1

Il suffit de faire la problématique DATEADD en deux étapes, en commençant par une unité de temps plus grossière (secondes, minutes, heures, etc.), puis en revenant à l'unité de temps à grain fin pour le reste.

Évitez toutefois d'aller jusqu'au niveau des semaines et des mois, car cela nécessiterait de véritables calculs de calendrier et nous préférerions que le système s'en charge.

L'exemple ci-dessous doit permettre de calculer une heure de début à partir d'une durée courante (éventuellement) importante en millisecondes.

-- large durations can overflow the integer argument needed for DATEADD

-- so do as two steps subtracting minutes (60000ms) and then remaining milliseconds.

DATEADD(ms, -large_duration_ms%60000, DATEADD(minute, -large_duration_ms/60000, GETDATE()))

Answer 2

Une façon de contourner le problème du dépassement d'entier a été de soustraire une date plus récente de l'horodatage microtime unix.

DATEADD(s, (CreationTimeStamp/1000-1384128000), '2013-11-11') AS CreateDate,

Cela ne résoudra pas le problème de l'OP parce qu'ils dépasseront toujours le maximum de la colonne de la date.

Answer 3

Selon le MSDN , en DATEADD (datepart , number , date )

nombre est une expression qui peut être résolu en un int t à une partie de date. Les variables définies par l'utilisateur sont valables. Si vous spécifiez une valeur avec une fraction décimale, la fraction est tronquée et n'est pas arrondie. pas arrondie.

Notez également que même si vous donnez un nombre entier, en fonction de la date et de la partie de la date, il peut dépasser la plage maximale de la date, qui est de 31-12-9999 pour sql server 2008.

Le nombre doit être un entier. Voici un Test Demo

Answer 4

J'ai eu le même problème et je voulais respecter la plage de dates de mssql.

• Date minimale : 1753-01-01 00:00:00.000 (-6847804800)
• Date maximale : 9999-12-31 23:59:59.997 (253402300799)

Pour y parvenir, la seule solution que j'ai trouvée a été d'utiliser en boucle DATEADD avec des valeurs en int.

Sur la base de cette réponse : https://stackoverflow.com/a/2904294/687490

CREATE FUNCTION dbo.fn_ConvertToBigDateTime (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @result datetime = Convert(datetime, '01/01/1970');

    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetime BIGINT
           ,@MinIntValue INT
           ,@MaxIntValue INT
           ,@RemainingSeconds BIGINT;

    -- define int limit
    SET @MinIntValue = -2147483648;
    SET @MaxIntValue = 2147483647;

    -- compute the datetime with the offset
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
    SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset

    -- going to the future
    WHILE(@AdjustedLocalDatetime>@MaxIntValue)
    BEGIN
        SET @AdjustedLocalDatetime = @AdjustedLocalDatetime - @MaxIntValue;
        SELECT @result = Convert(datetime, dateadd(ss, @MaxIntValue,@result));
    END

    -- going back in the past
    WHILE(@AdjustedLocalDatetime<@MinIntValue)
    BEGIN
        SET @AdjustedLocalDatetime = @AdjustedLocalDatetime - @MinIntValue;
        SELECT @result = Convert(datetime, dateadd(ss, @MinIntValue,@result));
    END

    RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, @result))
END;

Vous pouvez ensuite tester la fonction avec :

select dbo.fn_ConvertToBigDateTime(-6847804800) as 'min datetime', 
dbo.fn_ConvertToBigDateTime(253402300799) as 'max datetime'

J'espère que cela vous aidera.

Answer 5

J'ai également été confronté à ce problème. Dans ma déclaration SQL, l'erreur se produit lorsque la valeur de la date et de l'heure est nulle.

Ma solution consiste à vérifier si la valeur de la date et de l'heure est nulle en utilisant "CASE When". Ne lancer l'arithmétique que si la valeur n'est pas nulle, et le problème est résolu.