代码之家 › 专栏 › 技术社区 › n8wrl

SQL Server日期时间参数“舍入”警告

stored-procedures datetime sql-server

n8wrl · 技术社区 · 15 年前

更多的是警告而不是问题:

今天早上我们解决了一个令人费解的问题。我们有各种各样的报告,允许用户输入他们想要运行的日期范围。假设是,如果您要求从2010年8月1日到2010年8月10日的报告,您打算包括 2010年10月8日,所以报告的截止日期不是2010年10月8日,而是之后的事情。

这不可能是2010年11月8日,因为有些报告会将一天中发生的所有事情汇总起来,并按午夜的那一天对它们进行分组,因此每日汇总将包括额外的一天,而不是我们想要的。

为了避免在接近当天结束时丢失任何项目的可能性,我们将结束日期计算为比明天少一个勾号:

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

从内部来看,结果是2010年10月8日下午12:59:59.9999

好吧,当您将这个datetime传递给SQL Server中的datetime参数时,它会将值四舍五入到8/11/2010 00:00:00!因为我们的查询使用

DateField BETWEEN @FromDate AND @ToDate

而不是

DateField >= @FromDate AND DateField < @ToDate

我们看到2010年8月1日至2010年8月10日的报告包括2010年8月11日的项目。

我们发现真正的问题的唯一方法是通过字符串来往返日期。datetime.toString()也会循环,所以我们最终得到了8/1/2010 12:59:59pm,这是SQL Server满意的结果。

所以现在我们的“结束一天”方法如下:

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

不好意思,没问题-只是觉得有人会发现它很有用。

4 回复 | 直到 15 年前

AdaTheDev 15 年前

这是因为日期时间数据类型的准确性,它具有准确性( quote ):

四舍五入到.000,.003的增量, 或007秒

所以是的,在某些情况下你必须小心(例如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纳秒。

当我在包含时间元素的日期时间字段上执行查询时,出于这个原因,我不使用between。

我更喜欢

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

而不是:

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

momo 15 年前

不幸的是,您发布的解决方案添加了另一个新的、微妙的问题,最终会回来咬你:现在,您将跳过所有日期,即>=23:59:59.003和<=23:59:59.997。我强烈地怀疑你可以减去小于1秒的东西,这是3个滴答,除非你做的是减少额外时间的事情。注意smalldatetime甚至不会存储秒。

在处理边界边缘时,利用表示该值的系统的“已知分辨率”来模拟具有包含值的排他端点是不好的。在转换具有不同分辨率的两个代表性系统时,您发现了这一点。最佳实践方法是直接向上到边界,但不包括边界,使用普通比较运算符。

所以,正如其他海报所说,正确的答案是使用 Dt >= @Dt1 AND Dt < @Dt2 . 我知道您有十亿个需要修复的存储过程,所以我可以建议以下方案来更正它:

更改您的函数以返回第二天,不带任何减法秒或刻度。

以编程方式为使用原始SP名称的所有存储过程构建“包装器”SP。将原始文件重命名为spname_dc(不符合日期)。在传递给ndc版本之前,从每个“结束日期”中减去3ms。

注意:您可以从系统表中获取sp参数类型和名称。这可能有助于您:

SELECT
   ObjectSchema = Schema_Name(SO.schema_id),
   ObjectName = SO.name,
   ObjectType = SO.Type_Desc,
   Position = P.parameter_id,
   ParameterName = P.name,
   ParameterDataType = Type_name(P.user_type_id),
   P.max_length,
   P.[Precision],
   P.Scale,
   P.Is_Output,
   P.Has_Default_Value,
   P.Default_Value
FROM
   sys.objects AS SO
   INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID
WHERE
   SO.Type = 'P'

现在,您可以慢慢地修复每个不符合的SP。使用包装纸不会有明显的性能问题。

包装器SP的示例如下:

CREATE PROCEDURE dbo.ProfitReport
   @FromDate datetime,
   @ToDate datetime = NULL OUT
AS
SET @ToDate = DateAdd(ms, -3, @ToDate)
DECLARE @RC int
EXEC @RC = dbo.ProfitReport_NDC @FromDate, @ToDate OUT
RETURN @RC

您需要获得所有日期参数的列表,并决定哪些参数代表结束日期边界。如果您的SP有任何XML或表值参数,请小心。

你可以再次回到理智的世界!

注意:如果升级到SQL 2008,在可以使用datetime2数据类型之前,无论如何都需要修复所有问题。

Philip Kelley 15 年前

SQL Server日期时间数据类型精确到333秒,即,.003,.006,.009等。这就是为什么你的.999四舍五入为0的原因。欢迎来到开发人员(又称“我们所有人”)的行列中,这个实现一次又一次地把它们搞得一团糟。

Martin Smith 15 年前

可存储的最大子秒值 datetime 是997。

所以使用 between 它需要(例如)

between '2010-08-27 00:00:00.000' and '2010-08-27 23:59:59.997'

你最好使用 < 而不是 之间 所以您的代码与 datetime2 此假设不成立的数据类型。