SQL Server - 在 ALTER TABLE 中将 VarChar 转换为日期

Question

我看到了一些关于这个问题的帖子，但我有一个具体的实例，我正在尝试在ALTER TABLE语句中进行转换。

ALTER TABLE Leads
ALTER COLUMN [Created Date] Date

出现了错误：

消息 241，级别 16，状态 1，行 34
将日期和/或时间从字符串转换时转换失败。
已终止该语句。

“创建日期”当前设置为（varchar（max），null）

- hansolo

---

新增一列（日期类型），复制数据，删除旧列。重命名新列。（注意依赖项、视图、索引和存储过程等问题。） - jarlh

你的代码中可能存在错误值。请尝试运行以下SQL语句：SELECT [Created Date] FROM Leads WHERE ISDATE([Created Date]) <> 1，以查找这些错误值。 - S3S

@scsimon 你说一个rouge的值？不是vert、jaune或者bleu吗？ - JohnHC

@JohnHC 诈骗者...有一个小错别字，天啊。 - S3S

@scsimon是正确的，许多其他人也提到了这一点。我有一个错误的值。有点尴尬，但感谢您帮助我认识到问题所在。 - hansolo

Answer 1

你可以将日期格式标准化。像这样：

UPDATE Leads
    SET [Created Date] = TRY_CONVERT(Date, [Created Date], 101);

第三个参数是针对评论中提到的MM/DD/YYYY格式。如果可能，它将把值转换为日期，否则将转换为NULL。然后，SQL Server将使用其默认格式转换回字符串。

注意：如果您这样做，请确保备份表格，以便不会丢失列中的信息！

然后，您的代码应该可以正常工作：

ALTER TABLE Leads ALTER COLUMN [Created Date] Date;

您可以使用以下方式找到错误的值:

select [Created Date]
from Leads
where try_convert(date, [Created Date], 101) is null and
      [Created Date] is not null;

- Gordon Linoff

Answer 2

根据Jarlh的说法：

ALTER TABLE Leads
ADD COLUMN CreatedDate Date;

UPDATE Leads
SET CreatedDate = cast(right([Created Date],4) + '-' + left([Created Date],2) + '-' + left(right([Created Date],7),2) as Date);

Alter TABLE Leads
DROP COLUMN [Created Date];

sp_rename 'Leads.CreatedDate', '[Created Date]', 'COLUMN';

- JohnHC

---

这里有一些严重的假设。如果值是'1/1/2016'怎么办？如果值有前导空格怎么办？显然，OP的列存在错误...但即使是这些例子也会被他的代码转换。 - S3S

@scsimon 是的，我没有考虑到那一点。 - JohnHC

Answer 3

我认为你必须创建临时表，然后将所有记录插入到临时表中，接着重命名临时表和原始表。

- Abouzar

---

你为什么这样想？ - S3S

1-创建临时表 2-将原始表中的所有记录插入并转换为临时表 3-重命名临时表和原始表 - Abouzar

这很有道理，可能是一种更安全的方法，但这不是OP所问的。 - charles ross