Member Avatar for A.Muqeetkhan

How would it be possible using T-SQL to find the raw amount of time that has passed between when the job started and when the job ended?

i have used this function

datediff(ss,grn_date,GETDATE()) .

this function gets the jobs done how ever if the diffrence btw the dates is huge like grn_date is in u know 2006-1-1 then i get the error that"Arithmetic overflow error converting int to data type numeric." so now what do i do to get the values.

  • 2 Contributors
  • 3 Replies
  • 207 Views
  • 5 Hours Discussion Span
  • Latest Post Latest Post by AleMonteiro

Dani AI

Generated

— that overflow is coming from the fact that DATEDIFF returns an INT, so very large intervals (depending on the datepart) can exceed the int range and raise errors. (learn.microsoft.com)

’s idea to use a coarser unit and multiply (minutes * 60) is a valid quick workaround, but it loses fractional seconds and is awkward. If you’re on SQL Server 2016 or later the clean fix is to use DATEDIFF_BIG, which returns a BIGINT and removes the int overflow problem:

-- SQL Server 2016+ — whole seconds
SELECT DATEDIFF_BIG(SECOND, grn_date, SYSDATETIME()) AS SecondsElapsed;

If you need sub-second precision, count microseconds and divide (cast to a decimal to keep fractions):

SELECT CAST(DATEDIFF_BIG(MICROSECOND, grn_date, SYSDATETIME()) AS DECIMAL(20,6)) / 1000000.0
       AS SecondsWithFraction;

DATEDIFF_BIG supports microseconds/nanoseconds and is documented on Microsoft Learn. (learn.microsoft.com)

If you must stay on an older SQL Server (no DATEDIFF_BIG), a safe pattern is to compute whole days as BIGINT then add the leftover seconds — this avoids pushing a single DATEDIFF call past the int limit:

SELECT
  CAST(DATEDIFF(day, grn_date, GETDATE()) AS BIGINT) * 86400
  + DATEDIFF(second,
             DATEADD(day, DATEDIFF(day, grn_date, GETDATE()), grn_date),
             GETDATE()) AS SecondsElapsed;

Finally, prefer storing timestamps as DATETIME2 or DATETIMEOFFSET and use SYSDATETIME()/SYSUTCDATETIME() for better precision and correct offset handling. See Microsoft’s date/time type docs for precision and range details. (learn.microsoft.com)

Recommended Answers

All 3 Replies

Member Avatar for AleMonteiro

DateDiff returns an Int not an BigInt, that's why you get the error.

One work arround is to get the minutes and then multiple by 60, to get the seconds, like this:

ABS(CONVERT(BigInt, (DateDiff(minute, grn_date, getDate()))) * 60)

I found the answer in this forum: http://www.sqlservercentral.com/Forums/Topic964359-392-1.aspx

And here it's a more sofisticated analysis: http://sqlanywhere.blogspot.com.br/2010/10/getting-bigint-from-datediff.html

Member Avatar for A.Muqeetkhan

thanks mates

Member Avatar for AleMonteiro

You're welcome.

Just mark the thread as solved please.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.