是 2012 年 3 月 27 日对 SQL Server 在 Varchar 到 Datetime 转换中具有重要意义

Question

我有一个存储过程，它接受一个作为字符串传入的日期时间参数。比如这样：

程序：

CREATE PROCEDURE [dbo].[MyFancySP]
   @MyStartDate datetime = NULL,
   @MyEndDate datetime = NULL
AS
....

叫：

EXEC [dbo].[MyFancySP]
   @MyStartDate = N'01/01/2012',
   @MyEndDate = N'03/01/2012'

存储过程一直像这样工作。现在，这是有趣的部分。一旦我将日期更改为 03/27/2012 或超过它，我就会收到以下错误：将 varchar 数据类型转换为日期时间数据类型会导致值超出范围。

存储过程中使用日期的唯一位置是 where 子句。如果它与它有关，我也会在这里复制它：

WHERE
((@MyStartDate IS NOT NULL AND @MyEndDate IS NOT NULL 
AND d.SomeDate >= @MyStartDate AND d.SomeDate <= @MyEndDate) 
OR @MyStartDate IS NULL AND @MyEndDate IS NULL)

知道为什么我会在 3 月 27 日或以后收到超出范围的例外吗？顺便说一下，这是在SQL Server 2008 R2上运行的。

谢谢！

Answer 1

在每个新数据库连接上执行以下命令。

SET DATEFORMAT DMY

执行此操作后，您的问题应该会消失。 我怀疑您的问题是由服务器区域设置以及每月的某一天是否是 13 日到 31 日的组合决定的。

您不仅看到了错误，还可能在没有注意到的情况下获取了不正确的数据;软件的其他层可能会纠正这一点，但可能只是在某些情况下。

Answer 2

d.SomeDate是什么类型？这是偶然NVARCHAR吗？这可以解释它，因为在这种情况下，WHERE 子句将包含一个隐式转换，数据类型优先级规则声明应作为DATETIME发生。错误的明显随机性是由于查询扫描d.SomeDate字段中具有无效日期的行这一简单事实。在这种情况下，您正在处理数据纯度问题，您应该修复表，最好将列设为 DATETIME。

另外：

• 始终在字符串表示中使用规范日期格式YYYYMMMDD（无分隔符）：EXEC [dbo].[MyFancySP] N'20120101', N'20120301'; 。此格式独立于主机区域设置、DATEFORMAT 和语言设置 。

• 阅读 T-SQL 中的动态搜索条件。 WHERE column=@value or @value is null 会阻止查询性能优化停滞不前。阅读文章。