question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked LiHongMSFT-3908 commented

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
sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ahmedsalah-1628
Any other questions about this Datediff overflow issue?
If not,please don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.


Thanks for your understanding!
Best regards,
LiHong

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

You can use datediff_big instead.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered LiHongMSFT-3908 edited

Hi @ahmedsalah-1628
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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.