sql-server - SQL Server 日期时间参数 'rounding' 警告

Question

与其说是问题，不如说是警告:

今天早上我们解决了一个非常令人费解的错误。我们有多种报告，允许用户输入他们想要运行的日期范围。假设是，如果您要求从 8/1/2010 到 8/10/2010 的报告，您打算包括 8/10/2010，因此报告的结束日期不是 8/10，而是在那之后.

不可能是 2010 年 11 月 8 日，因为其中一些报告汇总了一天中发生的所有事情，将它们按当天午夜分组，因此每日汇总将包括额外的一天 - 不是我们想要的。

为了避免在非常接近一天结束时丢失任何项目的可能性，我们将结束日期计算为比明天少“一个刻度”:

public static DateTime EndOfDay(DateTime day)
{
    return day.Date.AddDays(1).AddTicks(-1);
}

在内部，这最终类似于 8/10/2010 12:59:59.9999PM

好吧，当您将此 DateTime 传递给 SQL Server 中的 DATETIME 参数时，它会将值向上舍入到 8/11/2010 00:00:00!由于我们的查询使用

DateField BETWEEN @FromDate AND @ToDate

代替

DateField >= @FromDate AND DateField < @ToDate

我们看到 8/1/2010-8/10/2010 的报告包括 8/11/2010 的项目。

我们发现真正问题的唯一方法是通过字符串来回转换日期。 DateTime.ToString() 也是四舍五入，所以我们最终会得到 SQL Server 满意的 8/1/2010 12:59:59PM。

所以现在我们的“一天结束”方法看起来像这样:

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);
}

抱歉不是问题 - 只是认为有人可能会觉得它有用。

Answer 1

这是因为 DATETIME 数据类型的准确性，它具有准确性( quote ):

Rounded to increments of .000, .003, or .007 seconds

所以是的，您在某些情况下必须小心(例如，23:59:59.999 将四舍五入到第二天的 00:00，23:59:59.998 将四舍五入到 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 纳秒的更高准确度。

当我对包含时间元素的 DATETIME 字段进行查询时，出于这个原因，我不使用 BETWEEN。

例如我更喜欢

WHERE DateField >= '2010-08-27' AND DateField < '2010-08-28'

代替:

WHERE DateField BETWEEN '2010-08-27' AND '2010-08-27T23:59:59.997'