2012年3月27日对于Varchar到Datetime转换中的SQL Server有何重要意义？

I have a stored procedure that takes a datetime parameter which is passed in as a string. Such as this:

我有一个存储过程,它接受一个datetime参数,该参数作为字符串传入。如:

Procedure:

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

Call:

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

The stored procedure has been working like this forever. Now, here's the interesting part. As soon as I change the date to 03/27/2012 or past it, I get the following error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

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

The only place in the stored procedure where the dates are used is in the where clause. In case it has to do with it, I'll copy it in here as well:

存储过程中使用日期的唯一位置是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)

Any ideas why I'm getting the out of range exception on March 27th or beyond? This is running on SQL Server 2008 R2 by the way.

任何想法为什么我会在3月27日或之后超出范围例外?顺便说一句,这在SQL Server 2008 R2上运行。

Thanks!

2 个解决方案

#1

What type is d.SomeDate? Is it a NVARCHAR by any chance? That would explain it, as the WHERE clause would contain in such case an implicit conversion that the rules of Data Type Precedence state that should occur as a DATETIME. The apparent randomness of the error occurs due to the simple fact that the query scans rows that have invalid dates in the d.SomeDate field. In such a case you're dealing with data purity issues and you should fix your tables, preferably by making the column a DATETIME.

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

In addition:

always use the canonical date format YYYYMMMDD (no delimiters) in string representation: EXEC [dbo].[MyFancySP] N'20120101', N'20120301';. This format is independent of the host locale, DATEFORMAT and LANGUAGE settings, .

始终在字符串表示中使用规范日期格式YYYYMMMDD(无分隔符):EXEC [dbo]。[MyFancySP] N'20120101',N'20120301';.此格式独立于主机区域设置,DATEFORMAT和LANGUAGE设置。
Read Dynamic Search Conditions in T-SQL. WHERE column=@value or @value is null stops query performance optimizations dead on its track. Read the article.

在T-SQL中读取动态搜索条件。 WHERE column = @ value或@value为null会使查询性能优化停止在其轨道上。阅读文章。

#2

Execute the following on each new database connection.

在每个新数据库连接上执行以下操作。

SET DATEFORMAT DMY

After you do this, your problem should disappear. I suspect that your issue is conditioned by a combination of server locale, and whether the day-of-month is 13th to 31st or not.

执行此操作后,您的问题应该消失。我怀疑您的问题是由服务器区域设置的组合决定的,以及日期是否为13到31。

Not only that you see the error, you may also be fetching data for incorrect periods without noticing; other layers of your software may be correcting for that, but maybe only in some cases.

您不仅可以看到错误,还可能在不注意的情况下获取错误时间段的数据;您的软件的其他层可能正在纠正,但可能仅在某些情况下。

#1