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
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,489146 gold badges81 silver badges122 bronze badges
answered Aug 14, 2009 at 9:31
Chris JChris J
30.5k6 gold badges68 silver badges111 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,21015 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
9792 gold badges12 silver badges25 bronze badges
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: Переполнение поля ‘DATEDIFF'»
Вот текст запроса.
ВЫБРАТЬ
ik_СостояниеПодменныхАвто.Автомобиль,
ik_СостояниеПодменныхАвто.ДокументВыдачи.Контрагент,
ik_СостояниеПодменныхАвто.ДокументВыдачи,
ik_СостояниеПодменныхАвто.ДокументВыдачи.ПробегПоДокументу,
РАЗНОСТЬДАТ(ik_СостояниеПодменныхАвто.ФактДатаВыдачи, ik_СостояниеПодменныхАвто.ФактДатаПриема, СЕКУНДА) / 86400 КАК ДниВИспользовании,
ik_СостояниеПодменныхАвто.ЦельВыдачи,
ВЫБОР
КОГДА ik_СостояниеПодменныхАвто.ТекущееСостояние = ЗНАЧЕНИЕ(перечисление.СостояниеПодменногоАвтомобиля.Свободен)
ТОГДА «Завершенная»
ИНАЧЕ «Активная»
КОНЕЦ КАК СостояниеПодмены
ИЗ
РегистрСведений.ik_СостояниеПодменныхАвто КАК ik_СостояниеПодменныхАвто
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,335146 gold badges85 silver badges121 bronze badges
answered Aug 14, 2009 at 9:31
Chris JChris J
30.3k6 gold badges68 silver badges109 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
JazzJazz
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,3931 gold badge14 silver badges23 bronze badges
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,335146 gold badges85 silver badges121 bronze badges
answered Aug 14, 2009 at 9:31
Chris JChris J
30.3k6 gold badges68 silver badges109 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
JazzJazz
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,3931 gold badge14 silver badges23 bronze badges
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,335146 gold badges85 silver badges121 bronze badges
answered Aug 14, 2009 at 9:31
Chris JChris J
30.3k6 gold badges68 silver badges109 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
JazzJazz
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,3931 gold badge14 silver badges23 bronze badges
0
При формировании отчета на СКД получили ошибку: Microsoft SQL Server Native Client 11.0: Добавление значения в столбец «datetime» привело к переполнению
Подробнее текст такой:
... по причине:
Ошибка компоновки данных
по причине:
Ошибка получения данных
по причине:
Ошибка создания набора данных "ВремяРаботы"
по причине:
Ошибка при исполнении запроса набора данных
по причине:
Ошибка выполнения запроса
по причине:
Ошибка при выполнении операции над данными:
Microsoft SQL Server Native Client 11.0: Добавление значения в столбец "datetime" привело к переполнению.
HRESULT=80040E07, HRESULT=80040E07, SQLSrvr: SQLSTATE=01003, state=1, Severity=0, native=8153, line=1
SQLSrvr: SQLSTATE=22007, state=1, Severity=10, native=517, line=1[/pre]В файловой базе это выглядит так:
Ошибка создания набора данных "ВремяРаботы"
по причине:
Ошибка при исполнении запроса набора данных
по причине:
Ошибка выполнения запроса
по причине:
Ошибка при выполнении операции над данными:
Ошибка SQL: Переполнение поля 'DATEDIFF'
по причине:
Ошибка SQL: Переполнение поля 'DATEDIFF'[/pre]После долгих поисков проблемы, опытным путем было выяснено: Ошибка возникает в момент попытки в запросе отнять от даты несколько секунд. Решения два:
1. не использовать в запросе: ДОБАВИТЬКДАТЕ или РАЗНОСТЬДАТ
2. на базе SQL при создании указывать смещение дат 2000
SELECT CONVERT(VARCHAR(10), DATEADD(SECOND, DATEDIFF(SECOND,DateTimeOn,DateTimeOff),0), 108)
The above query runs and brings back values but equally return 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.»
asked Jun 10, 2014 at 13:18
The problem occurs when the dates are more than 68 years apart. From the documentation:
If the return value is out of range for int (-2,147,483,648 to
+2,147,483,647), an error is returned. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31
minutes and 23.647 seconds. For second, the maximum difference is 68
years.
You can solve it with a case:
SELECT CASE WHEN DATEDIFF(YEAR,DateTimeOn,DateTimeOff) >= 68 THEN '>68 years'
ELSE CONVERT(VARCHAR(10), DATEADD(SECOND, DATEDIFF(SECOND,DateTimeOn,DateTimeOff),0), 108) END CASE
answered Jun 10, 2014 at 13:23
Klas LindbäckKlas Lindbäck
32.9k4 gold badges57 silver badges82 bronze badges
SELECT CONVERT(VARCHAR(10), DATEADD(SECOND, DATEDIFF(SECOND,DateTimeOn,DateTimeOff),0), 108)
The above query runs and brings back values but equally return 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.»
asked Jun 10, 2014 at 13:18
The problem occurs when the dates are more than 68 years apart. From the documentation:
If the return value is out of range for int (-2,147,483,648 to
+2,147,483,647), an error is returned. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31
minutes and 23.647 seconds. For second, the maximum difference is 68
years.
You can solve it with a case:
SELECT CASE WHEN DATEDIFF(YEAR,DateTimeOn,DateTimeOff) >= 68 THEN '>68 years'
ELSE CONVERT(VARCHAR(10), DATEADD(SECOND, DATEDIFF(SECOND,DateTimeOn,DateTimeOff),0), 108) END CASE
answered Jun 10, 2014 at 13:23
Klas LindbäckKlas Lindbäck
32.9k4 gold badges57 silver badges82 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.
Подскажите, кто встречался с подобной ошибкой! Что она означает? База файловая… УТ 11.0.7.8, доработанная, движок 8.2.15.294. {ОбщийМодуль.рамМодульСервер.Модуль}: Ошибка при вызове метода контекста (Выполнить) Выборка = Запрос.Выполнить.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам); по причине: Ошибка выполнения запроса по причине: Ошибка при выполнении операции над данными: Ошибка SQL: Переполнение поля по причине: Ошибка SQL: Переполнение поля
ну может кто нить что нить предположит для поддержания дискуссии? )))
может, действительно переполнилось поле? бывает.
а где то поле и чем отчерпывать идей нету?
числа итоговые большие получаются примерно?
юзай для всех числовых полей Выразить с четким указанием разрядености, да побольше. SQL в план запроса сам вставляет это, но разрядность выбирает сам исходя из своих соображений, возможно из-за этого
а кто ж его знает? запрос то не выполняется. ошибка начала вылетать после исправления соединения с таблицей свободных остатков с внутреннего на левое. до этого все в порядке было
попробуй ибо зло от ЕстьNULL, лни разрядность рушат +
поставил ВЫРАЗИТЬ КАК Число(15,2), не помогло ((( там же в базе если все просуммировать, меньше получится!
на все поля? покаж запрос
ты совесть то имей ЕСТЬNULL(СвободныеОстаткиОстатки.рамКПоступлениюОстаток, 0)* где ты приведение сделал ?
а тут СУММА(…..всё это дело) и выразить на всю её?
ёпрст! туплю, бывает. щазззз…
спасибо, помогло! плюс еще ошибка была, когда было внутреннее соединение, отбор на таблицу остатков работал и на документы. а сделал левое соединение, и поехали всю базу лопатить…
Тэги: 1С 8
Комментарии доступны только авторизированным пользователям