SQL Server DateTime参数'舍入'警告 [英] SQL Server DateTime parameter 'rounding' warning
问题描述
更多的警告不是一个问题：
More of a warning than a question:
我们今天早上解决了一个非常令人困惑的错误。我们有各种各样的报告，允许用户输入他们想要运行的日期范围。假设是，如果您要求2010年8月8日至2010年8月8日的报告，您意味着包括 8/10/2010，所以报告的结束日期不是8 / 10，这是之后的事情。
We resolved a very puzzling bug this morning. We have a variety of reports that allow users to enter date ranges they want to run. The assumption is, if you ask for a report from 8/1/2010 to 8/10/2010 you meant to include 8/10/2010 so the end-date of the report isn't 8/10, it's something after that.
它不能是8/11/2010因为这些报告中的一些汇总了一天中发生的一切，在当天将其分组这是在午夜，所以每天的汇总将包括一个额外的一天 - 不是我们想要的。
It can't be 8/11/2010 becuase some of these reports rollup everything that happened during a day grouping them by that day which is at midnight, so a daily rollup would include an extra day - not what we wanted.
为了避免错过任何非常接近尾声的项目的可能性当天，我们将结束日期计算为明天的一滴：
To avoid the possibility of missing any items very very close to the end of the day, we computed the end date as 'one tick' less than tomorrow:
public static DateTime EndOfDay(DateTime day)
{
return day.Date.AddDays(1).AddTicks(-1);
}
内部这样做会像8/10/2010 12：59：59.9999 PM
Internally this ends up something like 8/10/2010 12:59:59.9999PM
当您将此DateTime传递给SQL Server中的DATETIME参数时，将值UP到8/11/2010 00:00:00！而且由于我们的查询使用
Well, when you pass this DateTime to a DATETIME parameter in SQL Server it rounds the value UP to 8/11/2010 00:00:00! And since our query uses
DateField BETWEEN @FromDate AND @ToDate
而不是
DateField >= @FromDate AND DateField < @ToDate
我们看到8/1 / 2010-8 / 10/2010的报告包括8/11/2010。
We were seeing reports from 8/1/2010-8/10/2010 include items from 8/11/2010.
我们发现真正问题的唯一方法是通过一个字符串的日期。 DateTime.ToString（）也是圆的，所以我们最终会遇到SQL Server很满意的8/1/2010 12:59:59 PM。
The only way we discovered the real problem was by round-tripping the dates thru a string. DateTime.ToString() rounds too so we'd end up with 8/1/2010 12:59:59PM which SQL Server was happy with.
所以现在我们的'结束日期方法如下所示：
So now our 'end of day' method looks like this:
public static DateTime EndOfDay(DateTime day)
{
// Cant' subtract anything smaller (like a tick) because SQL Server rounds UP! Nice, eh?
return day.Date.AddDays(1).AddSeconds(-1);
}
对不起没有问题 - 只是想有人可能会发现它很有用。 >
Sorry not a question - just thought someone might find it useful.
推荐答案
这是因为DATETIME数据类型的准确性，具有准确性（ quote ）：
It's because of the accuracy of the DATETIME datatype, which has an accuracy (quote):
舍入为0.000，.003，
或.007秒的增量
Rounded to increments of .000, .003, or .007 seconds
所以是的，你做必须在某些情况下小心（例如23：59：59.999将被舍入到第二天的00:00，23：59：59.998将被舍入到23：59：59.997）
So yes you do have to be careful in certain situations (e.g. 23:59:59.999 will be rounded up to 00:00 of the following day, 23:59:59.998 will be rounded down to 23:59:59.997)
SELECT CAST('2010-08-27T23:59:59.997' AS DATETIME)
SELECT CAST('2010-08-27T23:59:59.998' AS DATETIME)
SELECT CAST('2010-08-27T23:59:59.999' AS DATETIME)
从SQL Server 2008起，有一个新的 DATETIME2 数据类型，其精度可达100纳秒。
As of SQL Server 2008, there is a new DATETIME2 datatype which gives greater accuracy down to 100 nanoseconds.
当我在DATETIME字段包含一个时间元素，因此不使用BETWEEN。
When I'm doing queries on a DATETIME field which contains a time element, I don't use BETWEEN for this reason.
eg我更喜欢
WHERE DateField >= '2010-08-27' AND DateField < '2010-08-28'
而不是：
WHERE DateField BETWEEN '2010-08-27' AND '2010-08-27T23:59:59.997'
这篇关于SQL Server DateTime参数'舍入'警告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
