DATEDIFF function resulted in an overflow in SQL Server 2016

Question

I have a table in SQL Server 2016 with the following data:

id     | t                       | memory
-------+-------------------------+------------
620255 | 2017-07-17 16:11:25.100 | 11632640
620127 | 2017-07-17 16:11:24.100 | 11632640
619999 | 2017-07-17 16:11:23.097 | 11632640
619872 | 2017-07-17 16:11:22.097 | 11632640

I am trying to get the average of the memory per second. So in order to do that I tried the below query:

SELECT 
    AVG(memory) AS avgMemory, 
    DATEADD(second, DATEDIFF(second, 0, t), 0) AS t 
FROM 
    Table1 AS Table1 
WHERE 
    t BETWEEN '2017-07-17 16:11:00.000' AND '2017-07-17 16:12:00.000' 
GROUP BY 
    DATEADD(second, DATEDIFF(second, 0, t), 0) 
ORDER BY 
    t ASC;

But upon execution, I get the following error:

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.

As per the official docs and this SO post, the maximum difference for seconds can be 68 years, but in my case it is just 60 seconds.

So why am I getting this error? And what can I do to resolve it?

Here is the SQLFiddle with the mentioned issue: Fiddle

To me, it looks like you're computing the difference between the date 0 and your t values. Did you want to compute the difference between the minimum t value and the current t value instead? — Frank Schmitt, Jul 18 '17 at 10:34
There is a problem with `DATEDIFF(second, 0, t)`. What do you want to achive with this? — Rokuto, Jul 18 '17 at 10:35
I am trying to group the timestamp on seconds/minutes/hours (depends on the user selection). — Prerak Sola, Jul 18 '17 at 10:47

Gordon Linoff · Answer 1 · 2017-07-18T11:53:39.790

1

SQL Server offers datediff_big() for this purpose:

SELECT AVG(memory) AS avgMemory,
       DATEADD(second, DATEDIFF_BIG(second, 0, t), 0) AS t 
FROM Table1 AS Table1 
WHERE t BETWEEN '2017-07-17 16:11:00.000' AND '2017-07-17 16:12:00.000' 
GROUP BY DATEADD(second, DATEDIFF_BIG(second, 0, t), 0) 
ORDER BY t ASC;

You could also do this by converting the value to a string:

SELECT AVG(memory) AS avgMemory,
       CONVERT(VARCHAR(19), t, 120) as t
FROM Table1 AS Table1 
WHERE t BETWEEN '2017-07-17 16:11:00.000' AND '2017-07-17 16:12:00.000' 
GROUP BY CONVERT(VARCHAR(19), t, 120)
ORDER BY t ASC;

This version works in all supported versions of SQL Server.

edited Jul 18 '17 at 11:53

answered Jul 18 '17 at 10:36

Gordon Linoff

1,242,037
58
646
786

Starting with 2016, right? And if he is on the previous version? – sepupic Jul 18 '17 at 10:39
1

@sepupic The OP explicitly mentioned SQL Server 2016. – Frank Schmitt Jul 18 '17 at 10:40
I'll try with this. – Prerak Sola Jul 18 '17 at 10:45
@Frank Schmitt When I wrote my comment there was no "2016", only "sql" and "sql server" – sepupic Jul 18 '17 at 10:47
@sepupic The question *body* was edited 44 minutes ago by the OP to include 2016 (your comment was added 39 minutes ago at the time I'm writing this) whereas the 2016 *tag* was indeed added by Gordon Linoff after your comment. – Frank Schmitt Jul 18 '17 at 11:20
@ Frank Schmitt really? Even now when I read your post, my comment has 43 minutes and post edits has 43 minutes – sepupic Jul 18 '17 at 11:25
The `DATEDIFF_BIG` can return difference of max 68 years for second. As I am subtracting from 0 (1900-01-01), it will still overflow. – Prerak Sola Jul 18 '17 at 11:51

score 1 · Answer 2 · answered Jul 18 '17 at 10:42

1

You can also use a different date (e.g. 2017-07-17 00:00:00.000) as the fixpoint for your calculations:

SELECT AVG(memory) AS memory, 
  DATEADD(second, 
          DATEDIFF(second, '2017-07-17 00:00:00.000', t),
          '2017-07-17 00:00:00.000') AS t 
FROM Table1 AS Table1 
WHERE t BETWEEN '2017-07-17 16:11:00.000' AND '2017-07-17 16:12:00.000' 
GROUP BY DATEADD(second, 
                 DATEDIFF(second, '2017-07-17 00:00:00.000', t), 
                 '2017-07-17 00:00:00.000') 
ORDER BY t ASC;

SQL Fiddle

answered Jul 18 '17 at 10:42

Frank Schmitt

30,195
12
73
107

The start and end dates come from the UI, so I cannot have a static date. – Prerak Sola Jul 18 '17 at 10:45
1

Sorry, I misunderstood your answer, but now I get what you are talking about. And it works. I changed the starting date to 2017-01-01 00:00:00.000 – Prerak Sola Jul 18 '17 at 11:54

score 1 · Accepted Answer · answered Jul 18 '17 at 10:46

1

The problem here is the "starting date".

In select DATEDIFF(second, 0, t) 0 means 1900-01-01 that is too distant from 2017, so the time in seconds passed from 1900-01-01 to 2017-07-17 overflows simple integer, it's not just "60 seconds"

answered Jul 18 '17 at 10:46

sepupic

8,409
1
9
20

Yes, thank you for the idea. I updated my starting date from 0 to 2017-01-01 00:00:00.000 as I know I don't have a timestamp before that in my table. – Prerak Sola Jul 18 '17 at 11:52

score 0 · Answer 4 · answered Sep 21 '19 at 21:48

0

pre 2016 db, on a group by, i had to cast the datediff as bigint then get the avg, for datediff by ms. avg(cast(datediff(ms,sdate,edate)) as BIGINT)) as theavg

answered Sep 21 '19 at 21:48

Robot70

600
6
10

DATEDIFF function resulted in an overflow in SQL Server 2016

4 Answers4