SQL 将表达式转换为数据类型日期时间的算术溢出错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/633848/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Arithmetic overflow error converting expression to data type datetime
提问by oekstrem
This select statement gives me the arithmetic error message:
这个选择语句给了我算术错误信息：
SELECT CAST(FLOOR((CAST(LeftDate AS DECIMAL(12,5)))) AS DATETIME), LeftDate
FROM Table
WHERE LeftDate > '2008-12-31'
While this one works:
虽然这个有效：
SELECT CAST(FLOOR((CAST(LeftDate AS DECIMAL(12,5)))) AS DATETIME), LeftDate
FROM Table
WHERE LeftDate < '2008-12-31'
Could there be something wrong with the data (I've checked for null values, and there are none)?
数据是否有问题（我检查了空值，但没有）？
回答by oekstrem
Found the problem to be when a date was set to 9999-12-31, probably to big for the decimal to handle. Changed from decimal to float, and every thing is working like a charm.
发现问题出在日期设置为 9999-12-31 时，可能太大以至于无法处理小数点。从十进制更改为浮点数，一切都像魅力一样工作。
回答by MatBailie
In general, converting a date to a numeric or string, to perform date operations on it, is highly inefficient. (The conversions are relatively intensive, as are string manipulations.) It is much better to stick to just date functions.
通常，将日期转换为数字或字符串以对其执行日期操作是非常低效的。（转换相对密集，字符串操作也是如此。）坚持只使用日期函数要好得多。
The example you give is (I believe) to strip away the time part of the DateTime, the following does that without the overhead of conversions...
你给出的例子是（我相信）去掉日期时间的时间部分，下面没有转换的开销......
DATEADD(DAY, DATEDIFF(DAY, 0, <mydate>), 0)
This should also avoid arithmentic overflows...
这也应该避免算术溢出......
回答by oekstrem
Maybe this helps someone since my problem was a bit different.
也许这对某人有所帮助，因为我的问题有点不同。
The SELECT that was throwing this error had many nested SELECTs and many date comparisons with arithmetic operations like GETDATE() - CloseDate.
抛出此错误的 SELECT 有许多嵌套的 SELECT 和许多与算术运算的日期比较，例如GETDATE() - CloseDate.
The result of such operations was then being compared to '1900-01-01'again mentioned many times in the nested SELECTs.
然后将此类操作的结果'1900-01-01'与嵌套 SELECT 中多次提及的结果进行比较。
My solution was to declare variables for result of GETDATE()and datetimevariable for '1900-01-01'to avoid conversions.
我的解决方案是为 result ofGETDATE()和datetimevariable for声明变量'1900-01-01'以避免转换。
Declare @CurrentDate datetime = GetDate()
Declare @BlankDate datetime = '1900-01-01'
...
... @CurrentDate - CloseDate ...
... CloseDate <> @BlankDate ...
The DATEADD(DAY, DATEDIFF(DAY, 0, <mydate>), 0)bit from MatBailie's answer was also helpful.
在DATEADD(DAY, DATEDIFF(DAY, 0, <mydate>), 0)从MatBailie的回答位也很有帮助。
