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

See more:

SQL

SQL-Server

T-SQL

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.

so how to solve this issue please ?

What I have tried:

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