You can use datediff_big
instead.
Error the datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large.
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
3 answers
Sort by: Most helpful
-
Erland Sommarskog 116.5K Reputation points MVP
2022-03-02T22:17:04.207+00:00 -
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. -
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 useDATEDIFF()
as the number ofDATEADD (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