The Datediff function resulted in an Sql Server overflow

Question

I have the following appointment:

SELECT 
ISNULL(MAX(CONVERT(TIME(0), DATEADD(SECOND, DATEDIFF(SS, [INICOLIGACAO],[FINALLIGACAO]), 0))),'')  AS [DURACAO_CHAMADA]

WHERE
 CONVERT(DATE,[DIA]) = '2019-10-30' 
AND [CAMPANHA] = ('1') 
GO

When I run it returns the error:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Warning: Null value is eliminated by an aggregate or other SET operation.

however when I change from seconds to minutes or even hours it returns the data, however accurate the data in seconds.

When DATEDIFF_BIG() is placed it gives the error:

'Datediff_big' is not a recognized built-in Function name.

I’m taking the calls just from the day before

the select I make is

SELECT 
     ACIONAMENTO
    ,CPF
    ,DDD+TELEFONE
    ,CONTRATO
    ,ISNULL(MAX(CONVERT(TIME(0), DATEADD(SECOND, DATEDIFF(SECOND,[INICOLIGAÇÃO],[FINALLIGACAO]), 0))) ,'')  AS [DURACAO_CHAMADA]
    ,OPERADOR

FROM 
    TABELA

WHERE
    CONVERT(DATE,[inicioligacao]) = CONVERT(DATE,GETDATE()-1)
     AND [campanha] IN ('1')

Answer 1

If the goal is to calculate the duration in seconds of a phone call, then it looks like someone stayed for years making a phone call...

About the error message "The Datediff Function resulted in an overflow", I had this problem once and that’s when I became aware of the existence of the function DATEDIFF_BIG. It allows much higher values, but for me it was not the solution because it is available (I think) from the 2016 version of SQL Server and I needed something compatible with the 2008 version. So I had to create an outline solution, very simple until.

If the version of SQL Server you are using is 2016 (or newer), then try DATEDIFF_BIG(). But if the context is phone call duration, probably the processed data is wrong.

---

At the end there is a conversion to TIME(0); this causes a call that has lasted 24 hours or more to be recorded only part of hours, ignoring the upper part of 24 hours. For example, if a call lasted 25 hours, 59 minutes and 10 seconds the final result will be 1h59min10s

Here is SQL code to show error that may occur:

-- código #1
declare @I datetime, @F datetime;
set @I= '20100101 01:00';
set @F= '20100102 02:59:10';

SELECT @I, @F, 
       datediff (second, @I, @F), 
       convert (time(0), dateadd (second, datediff (second, @I, @F), 0));

---

To check the consistency of the data turn the following code:

-- código #2 v2
SELECT INICOLIGACAO, FINALLIGACAO,
       datediff (hour, INICOLIGACAO, FINALLIGACAO) as DURAÇÃO
  from tabela
  where cast (DIA as date) = '2019-10-30' 
        and CAMPANHA = '1'
        and datediff (hour, INICOLIGACAO, FINALLIGACAO) >= 24;

The above code lists calls whose duration was greater than or equal to 24 hours, which represents loss of information when converting to TIME(0).

---

To find out which version of SQL Server is in use as well as the level of database compatibility, run

-- código #3
PRINT @@version
--
SELECT name, compatibility_level
  from sys.databases
  where name = 'nomedobanco';

Substitute nomedobanco by name of the database.

Answer 2

DATEDIFF

... To according to, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds.

As the documentation itself says, the maximum difference between two dates in seconds is the one quoted above. Therefore, although I don’t know what the data in your table is, I conclude that the data contained in its columns are incorrect or that the date format presented is not the one expected by the database after all 68 years for a link is an inconsistent amount.

From the SQL Server 2016 you can use the function DATEDIFF_BIG that does not have a limit so "scarce".

DATEDIFF_BIG

This function returns the count (as a large integer value with sign) of the limits of datepart specified cross between the specified parameters startdate and enddate.

---

You can check the version of SQL Server executing the following command:

SELECT @@VERSION AS versao

@@VERSION

Returns build and system information for the current SQL Server installation.