在Server中查找导致类型转换中的错误的行

Question

我正在尝试将一个列转换为来自smalldatetime的varchar。有一些行包含错误，我如何找到它们？

SELECT 
    PA.EAR_TAG 
    ,ISNULL(B.DISPOSAL_DATE, H.DISPOSAL_DATE) as HB_Date
    ,Y.[DATE OF MOVEMENT] as Y_Date
    ,DATEDIFF(DAY, ISNULL(B.DISPOSAL_DATE, H.DISPOSAL_DATE), cast(Y.[DATE OF MOVEMENT] as smalldatetime))
FROM
    DairyTelomere.dbo.PROJECT_ANIMALS AS PA
LEFT JOIN 
    Langhill.dbo.YOUNG_STOCK_BULL AS B ON Pa.EAR_TAG = B.EAR_TAG
LEFT JOIN      
    Langhill.dbo.YOUNG_STOCK_HEIFER AS H ON PA.EAR_TAG = H.EAR_TAG
LEFT JOIN      
    DairyTelomere.dbo.Young_Stock_culls AS Y ON PA.EAR_TAG = Y.Ear_Tag

我得到的错误是：

Msg 242，第16级，第3状态，第1行 将varchar数据类型转换为小时间数据类型会导致超出范围的值。

我知道，如果列是日期格式，我可以使用ISDATE()检查它，但不幸的是，我不能更改列类型(没有权限)。

任何想法都将受到极大的赞赏。

Answer 1

您可以使用伊萨特获取所有不为您进行转换的列表。您不需要更改列类型来使用它，所以我对您的声明感到困惑。

如果列是日期格式，我可以使用ISDATE()检查它，但不幸的是，我不能更改列类型(不具有权限)

如果您可以澄清，则会有更多帮助，但此查询应该会为您提供一个日期值错误的行的列表。

select table.date_as_varchar
from table
where isdate(table.date_as_varchar) = 0

Answer 2

添加到@workabyte应答时，您还可以尝试使用TRY_PARSE、TRY_CAST或TRY_CONVERT，如果转换失败，它们都会返回NULL，这样您就可以知道是哪些行导致了错误。

TRY_PARSE

正如文档所说：

仅在将字符串转换为日期/时间和数字类型时使用TRY_PARSE。对于一般类型转换，请继续使用强制转换或转换。请记住，在解析字符串值时存在一定的性能开销。

使用示例：

SELECT TRY_PARSE(your_date  AS DATETIME USING 'es-ES') as date
FROM your_table

es-ES是一个区域性参数，不同的区域性参数在转换中产生不同的结果，您可以在文档中找到完整的参数列表。

TRY_CONVERT

正如文档所说：

TRY_CONVERT接受传递给它的值，并尝试将其转换为指定的data_type。如果转换成功，TRY_CONVERT将作为指定的data_type返回值；如果发生错误，则返回null。但是，如果您请求的转换是显式不允许的，那么TRY_CONVERT就会出现错误。

使用示例：

SELECT TRY_CONVERT(DATETIME,your_date,103) as date
FROM your_table

103是转换日期的样式/格式，这里可以找到格式的列表。

TRY_CAST

正如文档所说：

TRY_CAST接受传递给它的值，并尝试将其转换为指定的data_type。如果转换成功，TRY_CAST将作为指定的data_type返回值；如果发生错误，则返回null。但是，如果您请求的转换是显式不允许的，那么TRY_CAST就会出现错误。

使用示例：

SELECT TRY_CAST(your_date AS DATETIME) as date
FROM your_table