主页 / dba / 问题 / 149997
Accepted
Sir Swears-a-lot
Asked: 2016-09-19 19:17:38 +0800 CST

子查询中的日期比较失败：“超出范围的值”

  • 772

我有一个更新语句，它使用子查询来过滤记录。我的输入表在 varchar 字段中包含日期，其中一些无效。

CREATE TABLE [dbo].[input](
    [id] int,
    [START_DATE] [varchar](30) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(1, NULL) ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(2, '') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(3, '01 JUL 0201') ;
INSERT INTO [dbo].[input] ([id], [START_DATE]) VALUES(4, '01 JUL 2016') ;

在示例数据中，只有记录 4 有效。

我的查询：

select a.[id] 
FROM (
    select [id], convert(datetime, [START_DATE],106) as csd
    FROM [dbo].[input]
    where len([START_DATE]) > 0     
    and substring([START_DATE],7,5) > 2010
    and isdate([START_DATE]) = 1
    ) a
    Where a.csd < getdate()

我玩过各种条款来排除垃圾：

  • len > 0 排除空值和 ''。
  • 子串寻找合理的年份。
  • isdate 再次检查有效日期。

我确信还有其他更有效的方法可以做到这一点，但关键是子查询会执行并正确返回有效记录。在本例中，id = 4。有效日期为 2016 年 7 月 1 日。

外部查询现在应该只是将转换后的日期与今天的日期进行比较。但是我得到：

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

为什么？我错过了什么？

内部查询已经过滤并转换了我正在评估的值。

有没有更好的方法来过滤此列表以用于更新查询？

sql-server-2008 t-sql

1 个回答

  1. Best Answer

    执行顺序在很大程度上取决于查询优化器。因此，在您的情况下，即使您编写的 SQL 代码看起来像子查询会首先过滤掉无效日期，查询的实际执行情况也可能非常不同。

    您可以使用 option(force order) 运行您的查询，但要小心那个，非常小心，并且知道您实际在做什么。另一种选择是临时表，用于预选有效或无效日期，但这也可能非常昂贵。

    虽然感觉很脏，但您也可以比较字符串值，以免遇到此问题，只需确保使用将排序为字符串的代码页，就像排序为日期一样

    select a.[id] 
FROM (
    select [id], convert(varchar(23), convert(datetime, [START_DATE],106),121) as csd
    FROM [dbo].[input]
    where len([START_DATE]) > 0     
    and substring([START_DATE],7,5) > 2010
    and isdate([START_DATE]) = 1
    ) a
    Where a.csd < convert(varchar(23, getdate(), 121)

    总而言之，最好将 start_date 保存为实际日期类型。

    • 4

相关问题