SQL - 日期查询问题 - varchar 转换为 datetime 导致值超出范围

Question

SQL - 日期查询问题 - varchar 转换为 datetime 导致值超出范围

sqlt-sqldatetimesql-server-2008-r2

6

我有一个SQL查询，是由一位已经离开的同事编写的。该查询作为SSIS作业的一部分运行，但从本月开始出现以下错误：

将varchar数据类型转换为datetime数据类型时发生了超出范围的值错误。

查询本身只是一个基本的select语句，其中包含一个where子句，用于查找特定时间范围内（在@startdate和@enddate之间的日期范围内）的值。

确定时间范围的代码如下：

DECLARE     @RunDateTime datetime
DECLARE     @RunDate datetime
DECLARE     @StartDate datetime
DECLARE     @EndDate datetime
DECLARE     @Month int
DECLARE     @Year int
DECLARE     @strStartDate varchar(10)

SET     @RunDateTime = GetDate()
SET     @RunDate = cast(round(convert(real, @RunDateTime),0,1) as datetime)

IF                  DATEPART(d, @RunDate) = 16
    BEGIN       
                    SET @StartDate =  DATEADD(d, -15, @RunDate)
                    SET @EndDate = @RunDate
    END
ELSE
    BEGIN
                    IF      Month(@RunDate) = 1
                            SET @Month = 12
                    ELSE    
                            SET @Month = Month(@RunDate) - 1

                    IF      Month(@RunDate) = 1
                            SET @Year = Year(@RunDate) - 1
                    ELSE
                            SET @Year = Year(@RunDate)

                    SET @strStartDate = CONVERT(varchar(2), @Month)+ '/16/' + CONVERT(varchar(4), @Year)
                    SET @StartDate = CONVERT(datetime, @strStartDate, 101)
                    SET @EndDate = @RunDate
    END

这项工作每月运行两次。一次是在当月的16日，用于处理本月1日至15日的数据；另一次是在下个月1日，用于处理上个月16日到月底的数据。

根据我在网上找到的信息，strStartDate 使用 varchar 可能是罪魁祸首？我对 SQL 不够熟悉，不知道如何替换其中的所有转换内容？另外，有没有更好的方法确定月底日期，而不是获取运行时间？任何帮助都将不胜感激。

（补充一下，我们在 SQL Server 2008 R2 上运行此作业），我与数据库管理员核实过，他说 SQL 服务器上没有更改地域设置。

- Oryx

1

看起来你正在尝试计算上个月的第16天。与其进行字符串操作，不如尝试使用SET @StartDate = DATEADD(month,DATEDIFF(month,'20010101',@RunDate),'20001216')，它应该总是能找到它（保持两个字符串常量完全相同）。 - Damien_The_Unbeliever

1

你确定错误出现在你发布的代码部分吗？行号上的101表示@strStartDate应该是mm/dd/yyyy格式，而它确实是这种格式，所以不应该依赖于服务器的区域设置。 - sgmoore

@Damien_The_Unbeliever 是的，这个查询将始终在每月的第1天和第16天运行。如果它没有在一个月的第16天运行，那么它必须是第1天，因此我们希望StartDate值表示上个月的第16天。（如果查询在第1天运行，我们基本上只想获得上个月的第16天）我对SQL不是很熟悉，但您提供的代码是否适用于任何月份？还是只适用于12月？ - Oryx

@sgmoore 我绝对不确定，但是查询的其余部分为选择名称，开始日期，工作ID其中开始日期在 @StartDate 和 @EndDate 之间 - Oryx

1

@Oryx - 是的，它适用于任何月份。基本上，它计算了2001年1月1日和@RunDate之间发生了多少个月份。然后它将这同样数量的月份加到2000年12月16日。如果你想一想，这应该总是产生比@RunDate更早一个月的第16天。 - Damien_The_Unbeliever

1

其余查询出错的唯一可能性是startdate的类型为varchar且具有无效值。如果您可以直接运行SQL查询，则可以尝试运行SELECT Count(*) from xxx WHERE startdate BETWEEN '20121216' and '20130101'以查看是否会生成错误。此外，您可以运行代码部分并添加几个selects，例如Select @strStartDate和select @StartDate，以了解它正在做什么。 - sgmoore

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- marc_s · Accepted Answer

SQL Server支持许多格式-请参见CAST和CONVERT的MSDN Books Online。大多数这些格式都依赖于您的设置-因此，这些设置有时可能有效-有时则无效。

解决方法是使用（略微改进的）ISO-8601日期格式，该格式受SQL Server支持-无论您的SQL Server语言和日期格式设置如何，此格式始终有效。 ISO-8601格式由SQL Server支持，有两种类型：

YYYYMMDD仅用于日期（没有时间部分）；请注意：这里没有破折号！，这非常重要！YYYY-MM-DD不独立于您的SQL Server中的日期格式设置，因此在某些情况下将无法使用！

YYYY-MM-DDTHH:MM:SS表示日期和时间 - 注意：此格式中有短横线（但可以省略），并且使用固定的T作为DATETIME中日期和时间部分的分隔符。

适用于SQL Server 2000及更新版本。

如果您使用的是SQL Server 2008或更新版本，并且使用DATE数据类型（仅限DATE - 不是 DATETIME！），那么您确实也可以使用YYYY-MM-DD格式，并且在SQL Server中的任何设置下都可以正常工作。

另外：对于SQL Server 2008，建议尽可能使用DATETIME2（而不是DATETIME）。DATETIME2对于错误和/或不同格式（例如美国上午/下午格式等）的字符串解析更加宽容。

不要问我为什么这个话题如此棘手且有些令人困惑 - 这就是事实。但使用 YYYYMMDD 格式，您应该可以在任何版本的 SQL Server 和 SQL Server 中的任何语言和日期格式设置中正常使用。