What does this error mean and how can I avoid it?
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.
I am not using the datediff function. I am doing this query where Timestamp is a datetime type:
SELECT TOP 10 * from vSomeView
WHERE TimestampUTC >= '2009-08-13 22:17:00'
What could I be doing wrong?
I’m using SQL Server 2008.
asked Aug 13, 2009 at 23:21
9
SQL Server may be doing a DATEDIFF internally for the comparison, and if the two dates are much more than 68 years apart (and the internal DATEDIFF is by seconds), DATEDIFF can error as the output of DATEDIFF is an INT.
I’ve bumped into this before (using DATEDIFF directly) and resolved it by casting DATETIMEs to DECIMALs as follows:
DECLARE @d1 DATETIME
DECLARE @d2 DATETIME
DECLARE @n1 AS DECIMAL(38,20)
DECLARE @n2 AS DECIMAL(38,20)
SET @d1 = '2 Jan 2000 00:00:02'
SET @d2 = '1 Jan 2000 00:00:00'
-- @n1 and @n2 will hold the datetime in fractional form. The integer part
-- is the #days since 1 Jan 1900, whilst the fractional part is the time in
-- 1/86400's of a second (24 hours = 86400 seconds, so a fraction of 0.5
-- represents 12:00:00 noon precisely.
SELECT @n1 = CAST(@d1 AS DECIMAL(38,20)), @n2 = CAST(@d2 AS DECIMAL(38,20))
-- Now manipulate the fractional and integer parts of the time
-- to get the final seconds difference.
SELECT CAST(86400 AS DECIMAL(38,20)) * (@n1 - @n2)
Pang
9,490146 gold badges81 silver badges122 bronze badges
answered Aug 14, 2009 at 9:31
Chris JChris J
30.5k6 gold badges68 silver badges110 bronze badges
2
I had the same issue because one of the records in my table had a default value for a datetime field of 1900-01-01 00:00:00.000.
SELECT *
FROM Terminal
WHERE DATEDIFF(SECOND, LastCheckIn, GETDATE()) < 30
DATEDIFF in the where clause will be evaluated for all the records in the table and will overflow on the LastCheckIn with value 1900-01-01 00:00:00.000
I solved it by first evaluating DATEDIFF for a difference in YEARS < 1
This is the final query:
SELECT *
FROM Terminal
WHERE
DATEDIFF(YEAR, LastCheckIn, GETDATE()) < 1
AND
DATEDIFF(SECOND, LastCheckIn, GETDATE()) < 30
answered May 13, 2014 at 18:23
PiloPilo
1,20014 silver badges11 bronze badges
2
Thank you all for the pointers!
They made me recheck the vSomeView and it turns out that the vSomeView was doing a join between a view and some other tables. That view was doing a datediff to convert some datetime into a posix-style timestamp (seconds since epoch). Once I removed it, the query runs fine.
SQL Server 2016 added DATEDIFF_BIG() which returns bigint.
answered Mar 28, 2018 at 20:04
TaudrisTaudris
1,4131 gold badge15 silver badges23 bronze badges
I created function which should bypass this limitation on SQL versions lower than SQL2016 see here.
It should work similliar to DATETIMEDIFF_BIG in simple scenarios
answered Mar 3 at 9:09
mr Rmr R
9592 gold badges12 silver badges25 bronze badges
This article provides a solution to a problem you may occasionally encounter while using the DATEDIFF() function in SQL Server.
If you encounter 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.
It’s because the return value is too large. 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. Fortunately there’s an easy way to fix this.
The quickest and easiest way to fix this issue is to switch to the DATEDIFF_BIG() function. This function works exactly like DATEDIFF(), except that its return data type is a signed bigint. In other words, it can handle really big numbers.
Example
Let’s see if DATEDIFF() can tell us how many milliseconds are in a thousand years:
SELECT DATEDIFF(
millisecond,
SYSDATETIME(),
DATEADD(year, 1000, SYSDATETIME()) ) AS 'Milliseconds in 1000 years';
Result:
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.
Maybe not. Seems there are too many milliseconds to fit into an int.
Time for the big guns. DATEDIFF_BIG(), do your stuff…
SELECT DATEDIFF_BIG(
millisecond,
SYSDATETIME(),
DATEADD(year, 1000, SYSDATETIME()) ) AS 'Milliseconds in 1000 years';
Result:
Milliseconds in 1000 years -------------------------- 31556908800000
That’s better.
If you still get the above error message, you must be trying to return a really big number. In that case you’ll need to do at least one of the following:
- Use a shorter time period.
- Use a less precise datepart. For example, instead of trying to return the number of milliseconds, try it with just seconds.
Also note that you’ll need to be running at least SQL Server 2016 before you can use the DATEDIFF_BIG() function.
I am trying to write a query that extracts records in a certain time period. I am working with lab data during a patient’s hospital stay. The patient can have lab work done prior to admission and these will be included as part of his stay; he could also have lab work done, but the results are returned until after the patient leaves the hospital and we would want those results as well.
More specifically, I want records 7 days before and one day after lab results are returned. For the 7 days before, I am using the hospital admission date and for the one day after I am using the hospital discharge date. For example, if a patient was admitted to the hospital 07/15/2009 and discharged 07/20/2009, I would want his lab results from 07/08/2009 (7 days prior) and 07/21/2009 (1 day after).
My dates are varchar(100) and I have a function that will convert these to sql datetime. My WHERE clause:
WHERE
( DATEDIFF(SECOND,DBO.VISTADATETODT(LABS.DATE),DBO.VISTADATETODT(PTF.DATE) ) <= 604800 )
AND ( DATEDIFF(SECOND,DBO.VISTADATETODT(PTF.DDTE),DBO.VISTADATETODT(LABS.DATE) ) <= 86400 )
LABS.DATE = date of the lab results
PTF.DATE = hospital admission date
PTD.DDTE = hospital discharge date
604800 is the number of seconds in 7 days and 86400 is the number of seconds in 24 hours.
I searched the net and saw where I should use a CAST function to cast DATETIME’s to DECIMALS and then back to seconds. Here is my feeble attempt which doesn’t work:
WHERE ( CAST( 86400 AS DECIMAL(38,20) ) *
( CAST( DBO.VISTADATETODT(LABS.DATE)AS DECIMAL(38,20) ) ) —
( CAST( DBO.VISTADATETODT(PTF.DATE)AS DECIMAL(38,20) ) ) ) <= 604800
I realize this is for only the first part of my date range (7 days before). Can someone please help fix my query? The above WHERE clause returns zero rows and I know I have rows that should be selected.
Thanks for your time.
Dave
The function DATEDIFF returns a signed integer, which can hold values from -2.147.483.648 to 2.147.483.647. If the dates you are applying the function to and the unit you are using (month, day, second, etc.) generate a difference outside these bounds then an error is thrown.
There are a few workarounds:
- Use DATEDIFF_BIG if you are using SQL Server 2016+.
- 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. You can then drill down the unit to the one you need by multiplying and handling the value as
BIGINT(for example). - It’s common for this error to pop up when comparing dates that are not valid to the business or generated by default as
1900-01-01. You can filter these with aWHEREclause, supply a decent default value or convert toNULL. Can also avoid applying theDATEDIFFfunction with aCASEbefore it when dates aren’t reasonable.
Examples:
DECLARE @OldDate DATE = '1900-01-01'
DECLARE @Now DATE = GETDATE()
SELECT DATEDIFF(SECOND, @OldDate, @Now) AS DateDiffResult
--Msg 535, Level 16, State 0, Line 5
--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.
Change the unit from second to minute:
DECLARE @OldDate DATE = '1900-01-01'
DECLARE @Now DATE = GETDATE()
SELECT DATEDIFF(MINUTE, @OldDate, @Now) AS DateDiffResult
-- DateDiffResult: 62599680
Revert the minute to second with a «bigger» data type:
DECLARE @OldDate DATE = '1900-01-01'
DECLARE @Now DATE = GETDATE()
SELECT
CONVERT(BIGINT, DATEDIFF(MINUTE, @OldDate, @Now)) * 60 AS DateDiffResult
-- DateDiffResult: 3755980800
I have this stored procedure in SQL-Server 2014
USE [provbank]
GO
/****** Object: StoredProcedure [dbo].[GetHBSeconds] Script Date: 2018-10-05 08:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetHBSeconds]
AS
BEGIN
DECLARE @hbdatetime datetime
DECLARE @diffSec int
SET @hbdatetime = (SELECT HBDateTime from HBData WHERE ID=1)
SET @diffSec = datediff(second,@hbdatetime, convert(datetime,convert(char(19), getdate(),126)))
SELECT @diffSec AS DiffInSeconds , @hbdatetime AS 'LastHB', convert(datetime,convert(char(19), getdate(),126)) AS 'CurrentTime'
END
And every now and then I get this 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.
What I have in the database is one row with a datetime column and an id.
I have a separate program that writes in this row the current timedate like ‘2018-10-05 09:58:30.000’. And then I have the SP to calculate between current time and the timestamp to get # of seconds in difference between now and the timestamp. I don’t think having the diff in seconds would be too narrow/too precise.
Is there something I can change in my SP to avoid this error?
Because the difference in the table and getdate() on the same machine/server shouldn’t be more then a few seconds apart if all works, which it is, when I get this error.