Error the datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large.

ahmed salah 3,216 Reputation points
2022-03-02T16:17:16.63+00:00

i work on sql server 2019 i face issue
when run query to
get queries run on server
i get error

Msg 535, Level 16, State 0, Line 1
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.

query i run it

SELECT
    p.spid,
    RIGHT(CONVERT(varchar, DATEADD(ms, DATEDIFF(ms, p.last_batch, GETDATE()), '1900-01-01'), 121), 12) AS [batch_duration],
    p.[program_name],
    p.hostname,
    MAX(p.loginame) AS loginame,
    (SELECT SUBSTRING(text, COALESCE(NULLIF(spid.stmt_start, 0), 1) + 1, CASE spid.stmt_end WHEN -1 THEN DATALENGTH(text) ELSE (spid.stmt_end - spid.stmt_start) END) FROM ::fn_get_sql(spid.[sql_handle])) AS [sql]
FROM
    master.dbo.sysprocesses p
    LEFT JOIN (
        SELECT
            ROW_NUMBER() OVER(PARTITION BY spid ORDER BY ecid) AS i,
            spid,
            [sql_handle],
            CASE stmt_start WHEN 0 THEN 0 ELSE stmt_start / 2 END AS stmt_start,
            CASE stmt_end WHEN -1 THEN -1 ELSE stmt_end / 2 END AS stmt_end
        FROM sys.sysprocesses
    ) spid ON p.spid = spid.spid AND spid.i = 1
WHERE
    p.spid > 50
    AND p.status NOT IN ('background', 'sleeping')
    AND p.cmd NOT IN ('AWAITING COMMAND', 'MIRROR HANDLER', 'LAZY WRITER', 'CHECKPOINT SLEEP', 'RA MANAGER')
GROUP BY
    p.spid,
    p.last_batch,
    p.[program_name],
    p.hostname,
    spid.stmt_start,
    spid.stmt_end,
    spid.[sql_handle]
ORDER BY
    batch_duration DESC,
    p.spid
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,429 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,689 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 116.5K Reputation points MVP
    2022-03-02T22:17:04.207+00:00

    You can use datediff_big instead.

    5 people found this answer helpful.

  2. Guoxiong 8,206 Reputation points
    2022-03-02T16:51:43.46+00:00

    The function DATEDIFF returns an integer. The biggest number of the integer in SQL is 2,147,483,647. So if you use the second (s) as the datepart to calculate the differences between two dates,

    SELECT DATEDIFF(s, '2020-01-01 10:10:10.000', '2022-03-02 11:45:00.000');
    

    it returns 68,348,090. But if you use the millisecond (ms) as the datepart, you should get 68,348,090,000 which is bigger than the maximum of the interger. That is why you would get the error message:

    Msg 535, Level 16, State 0, Line 1
    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.

    2 people found this answer helpful.
    0 comments No comments

  3. LiHong-MSFT 10,051 Reputation points
    2022-03-03T02:28:09.357+00:00

    Hi @ahmed salah
    As Guoxiong answered, The DATEDIFF() function returns its result as an int data type. The reason you got this message is that the return value is too big for the int data type.
    There are a few workarounds:
    1.Move to a "higher" unit (milliseconds -> seconds -> minutes -> hours and so on) until the value you get can be cast into a integer and make sure that all the values you might apply the function to in the future will still be inside the bounds of an integer.
    2.Use DATEDIFF_BIG function if you are using SQL Server 2016+.It returns the bigint difference between the startdate and enddate, expressed in the boundary set by datepart.
    However,in your sitution ,you use DATEDIFF() as the number of DATEADD (datepart ,number, date ).And for the number type in 'DATEADD ',it must be INT.
    Therefore,you can't use DATEDIFF_BIG like this:

    RIGHT(CONVERT(varchar, DATEADD(ms, DATEDIFF_BIG(ms, p.last_batch, GETDATE()), '1900-01-01'), 121), 12) AS [batch_duration]  
    

    You may get this error: Arithmetic overflow error converting expression to data type int.
    So,you need to chang your code like this:

    RIGHT(CONVERT(VARCHAR, DATEADD(ms,   DATEDIFF_BIG(ms,p.last_batch, GETDATE())%86400000,   DATEADD(dd,DATEDIFF_BIG(ms,p.last_batch, GETDATE())/86400000,'1900-01-01')),  121) ,12)  
    

    Best regards,
    LiHong

    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.