SQL Server: datediff function resulted in an overflow when using MILLISECOND

Question

I have the following query :

select CONVERT(varchar(12), DATEADD(MILLISECOND, DateDiff(MILLISECOND, '2014-08-04 10:37:28.713','2014-11-04 08:21:17.723'), 0), 114)

When I execute this, I get the 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."

When I change the query to the following it works fine :

select CONVERT(varchar(12), DATEADD(SECOND, DateDiff(SECOND, '2014-08-04 10:37:28.713','2014-11-04 08:21:17.723'), 0), 114)

The problem is that I really need the MILLISECONDS as well.

DATEDIFF returns an integer, which simply isn't big enough to hold the result. The only way I can think of would be to work out how many days there are between the two dates, then do a comparison on only the time portions of the two dates to work out how many milliseconds difference there is, and add the two together (multiplying the number of days by the number of milliseconds in a day). — Alan, Nov 04 '14 at 08:05
Side note: Are you working in an area which applies some kind of daylight savings? Because in a lot of places, there's been a transition between on/off of DST during the period you're looking at and SQL Server will not take it into account. So you could end up with a result that is "accurate" to the millisecond but off by an entire hour. — Damien_The_Unbeliever, Nov 04 '14 at 08:16
@Damien_The_Unbeliever this is why you should normalize date information on the DB record level, and transform appropriately in presentation to the user's locale. — defines, Jul 27 '16 at 18:11

score 46 · Answer 1 · answered Apr 14 '16 at 10:03

46

A bit later response but may help. In SQL 2016 MS introduced function DATEDIFF_BIG which will (according to type size) overflow in difference bigger than something like 290k years. But technet article have same time difference as basic DATEDIFF - https://msdn.microsoft.com/en-us/library/mt628058.aspx

answered Apr 14 '16 at 10:03

jarabizna

461
4
2

Awesome, I wasn't aware of this addition in 2016. Bit early for us to start using it, but good to know it's on the horizon :) – defines Jul 27 '16 at 18:09

score 30 · Accepted Answer · edited Jun 04 '20 at 01:51

30

See https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver15#return-value

For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds.

If you need millisecond above that level, you'll need to write something custom.

edited Jun 04 '20 at 01:51

Pang

9,564
146
81
122

answered Nov 04 '14 at 08:08

Marcel Dumont

1,217
1
11
17

score 19 · Answer 3 · answered Nov 07 '18 at 17:14

19

In SQL Server 2016 there is a new function available: DATEDIFF_BIG

It solves exactly the overflow problem.

answered Nov 07 '18 at 17:14

tomfroehle

602
5
16

t-clausen.dk · Answer 4 · 2014-11-04T08:37:44.577

5

You don't need to refer to the miliseconds in your calculation.

This will do exactly the same as your script except the overflow:

SELECT CONVERT(varchar(12), 
        CAST('2014-11-04 08:21:17.723' as datetime) - 
        CAST('2014-08-04 10:37:28.713' as datetime)
       , 114)

edited Nov 04 '14 at 08:37

answered Nov 04 '14 at 08:26

t-clausen.dk

43,517
12
59
92

score 3 · Answer 5 · answered Mar 24 '17 at 12:41

3

For me there was a big interval between two dates so i have used below code

declare @timetagInMillsecond bigint=CAST(CAST( cast(@timetag as datetime) -'1970-01-01' AS decimal(38,10))*24*60*60*1000+0.5 as bigint)

It works for me .

answered Mar 24 '17 at 12:41

Ashwini Jindal

811
8
15

Er. Binod Mehta · Answer 6 · 2021-11-01T08:17:46.273

2

Use DATEDIFF_BIG to resolve the overflow issue

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.

SELECT DATEDIFF_BIG( 
    millisecond, 
    SYSDATETIME(), 
    DATEADD(year, 1000, SYSDATETIME()) ) AS 'Milliseconds in 1000 years';

edited Nov 01 '21 at 08:17

answered Nov 01 '21 at 06:55

Er. Binod Mehta

57
9

user2864740 · Answer 7 · 2020-05-09T20:50:42.380

For SQL Server 2014, the following works around the 'int' limitation to obtain a "JavaScript Time Epoch". This assumes the start epoch is itself a date, which fits the local use-case leading to finding this question. The query requires adaptation to the specific question use-case which does not have this property.

declare @x as datetime = getdate()

-- epoch_delta_s_to_date * 1000 + day_delta_ms
select
cast(datediff(second, '1970-01-01', cast(@x as date)) as bigint) * 1000
  + cast(datediff(millisecond, cast(@x as date), @x) as bigint)

For the case of obtaining a "JavaScript Time Epoch" this is still subject to the Y2038 limitation of datediff(second, '1970-01-01', ..).

score 0 · Answer 8 · answered Mar 01 '23 at 12:29

I have to use DATEDIFF_BIG in SQL versions before SQL2016 and have written my own function:

Declare @datestart datetime = '1953-01-01 23:18:09.284'
Declare @dateend datetime = '9999-12-31 23:54:03.844'

select 
/*DATEDIFF_BIG(millisecond, @datestart, @dateend) AS [ForTestComparion], */
(cast(DATEDIFF(DAY, @datestart, @dateend) as bigint) * 24 * 60 * 60 * 1000 )
+ 
    DATEDIFF(millisecond, 
    DATETIMEFROMPARTS(2000, 1, 1,DATEPART(HOUR, @datestart), DATEPART(MINUTE, @datestart), DATEPART(SECOND, @datestart), DATEPART(MILLISECOND, @datestart)),
    DATETIMEFROMPARTS(2000, 1, 1,DATEPART(HOUR, @dateend), DATEPART(MINUTE, @dateend), DATEPART(SECOND, @dateend), DATEPART(MILLISECOND, @dateend)))

2000, 1, 1 can be any date just need to be same day to compare only hours,minute,second,milisecond

SQL Server: datediff function resulted in an overflow when using MILLISECOND

8 Answers8

Linked