主页 / dba / 问题 / 257303
Accepted
Racer SQL
Asked: 2020-01-14 10:50:12 +0800 CST

为什么每次运行时从 varchar do date 转换都会在不同的行上失败？

  • 772

我有一个这样的字段的数据库（不知道为什么它是 varchar）：

19950105

我正在尝试将其转换为 DATE 作为 DDMMYYYY。

我可以这样做：

convert(varchar(10), cast(The_ugly_field as date),103)

然后我的日期是：

1995 年 5 月 1 日

1）为什么我不能直接将其转换为日期？如果我使用：

select convert(date,The_ugly_field,103) from Ugly_Table

它返回1900-01-01并且不尊重 103。那里的任何数字都返回相同的格式 YYYY-MM-DD。

2）现在真正的问题。当我跑步时 

select  convert(varchar(10), cast(The_ugly_field as date),103)  
From Ugly_Table
order by The_ugly_field

一段时间后它返回此错误：

消息 241，级别 16，状态 1，第 29 行从字符串转换日期和/或时间时转换失败。

但有趣的是，错误看起来并没有出现在同一行。如果我运行此代码 4 次，则每次返回错误时，都会显示不同的行。我的意思是，我执行它，然后在“结果”选项卡中我可以看到它返回了1.234.555 rows. 如果我再次运行它，在错误之后我返回到“结果”表，现在查询运行到1.556.345.

请。我怎么能20140101以优雅的方式将其转换为2014/31/12？跑步convert(date,E1_EMISSAO,103)似乎不尊重103and 它返回2017-02-24而不是24/02/2017。

大量的问题和答案，但都是关于一个简单的转换（）。

sql-server-2008-r2

1 个回答

  1. Best Answer

    处理不同数量的行后发生错误的原因是转换不是以确定的顺序完成的。这取决于执行计划。

    尤其是如果查询是并行的（考虑到行数，我希望是这样），行将在每次运行时以不同的方式分布在线程中，并且每次都可能以不同的顺序命中“计算标量”。

    其他原因是可能使用了不同的索引（导致行的排序不同），尽管考虑到查询的静态性质，这似乎不太可能。

    执行此操作时格式化参数不起作用的原因：

    select convert(date,The_ugly_field,103) from Ugly_Table

    ...是因为您尝试使用的 103 样式用于将日期或日期时间转换为字符数据类型。不是日期的varchar（这就是 CONVERT 语句的作用）。在这种情况下，SQL Server 只是以实例的默认格式显示日期。

    您需要手动追踪无法转换的“坏”数据：

    • 可选：MAXDOP 1向查询添加提示，以使返回的数据顺序更具确定性
    • 查看查询成功返回的最后一行
    • 在没有转换的情况下执行您SELECT并手动检查该区域中的值
    • 如果可能，更正或删除不良数据
    • 向 中添加一条CASE语句以SELECT避免尝试转换错误的值，如下所示：
    select  CASE WHEN LEN(The_ugly_field) < 8 THEN '01/01/1900' ELSE convert(varchar(10), cast(The_ugly_field as date),103)  END
From Ugly_Table
order by The_ugly_field

    我会建议TRY_CONVERT，但这仅适用于 SQL Server 2012 及更高版本。

    • 3

相关问题