将nvarchar转换为日期时，从字符串转换日期和/或时间时转换失败

Question

我有一个名为usda_losses_temp的表，它有一个nvarchar(50)类型的列end_date，这些列是格式为yyyy-mm-dd的日期值。我正试图使用以下查询将这些数据放入一个名为end的新列中：

update usda_losses_temp 
set end = cast((substring(hazard_end_date, 1, 4) + '-'+ substring(hazard_end_date, 6, 2) + '-'+ substring(hazard_end_date, 9, 2)) as date)

然而，我得到的错误是：

从字符串转换日期和/或时间时转换失败。

示例查询：

update usda_losses_temp 
set end_date = cast((substring('2020-09-31', 1, 4) + '-'+ substring('2020-09-31', 6, 2) + '-'+ substring('2020-09-31', 9, 2)) as date)

我也试过

update usda_losses_temp 
set end = FORMAT((substring(hazard_end_date, 1, 4) + '-'+ substring(hazard_end_date, 6, 2) + '-'+ substring(hazard_end_date, 9, 2)),'yyyy-MM-dd')

但它给了我一个错误，说：

参数数据类型nvarchar对于格式化函数的参数1无效。

我无法使它工作。我该如何解决这个问题？

Answer 1

为什么不使用正确(120(格式的convert？

update usda_losses_temp set [end] =  convert(date, hazard_end_date, 120);

参见dbfiddle

如果这不起作用，那么你的数据中提供的格式有一些无效的日期。

Answer 2

无论应用何种格式或子字符串，都无法将示例日期'2020-09-31'转换为日期。9月只有30天

从而揭示了潜在的问题：这些从一开始就不应该被存储为字符串。

查找有问题的行：

SELECT * FROM usda_losses_temp WHERE ISDATE(hazard_end_date) = 0;

一旦修复了坏数据，就应该使用Dale建议的简单CONVERT, col, 120)语法，而不是所有这些乱七八糟的子字符串。

并添加一个检查约束(CHECK (ISDATE(hazard_end_date)) = 1(，直到您或他们可以修复该表为止。因为这才是真正的问题。

Answer 3

尝试使用convert((而不是cast:

update usda_losses_temp 
set end_date = CONVERT(datetime,(substring('2015-09-29', 1, 4) + '-'+ substring('2020-09-29', 6, 2) + '-'+ substring('2020-09-29', 9, 2)+'T23:59:59.000'), 126)

此外，"2020-09-31"向我抛出了一个来自varchar的转换异常。日期时间不会隐式转换为日期字段的日期。