SQL Server-将VarChar转换为ALTER TABLE中的日期 [英] SQL Server - Convert VarChar to Date in ALTER TABLE

问题描述

看到了一些线程，但是我有一个特定的实例，我试图在ALTER TABLE语句中进行转换。

Saw a few threads on this but I have a specific instance where I am trying to do the conversion within an ALTER TABLE statement.

ALTER TABLE Leads
ALTER COLUMN [Created Date] Date

错误：

消息241，级别16，状态1，行34

转换日期和/或时转换失败

语句已终止。

Msg 241, Level 16, State 1, Line 34
Conversion failed when converting date and/or time from character string.
The statement has been terminated.

创建日期当前设置为（varchar（最大值），则为null）

Created Date is currently set as (varchar(max), null)

推荐答案

您可以标准化日期格式。像这样的东西：

You could standardize the date format. Something like this:

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

第三个参数用于注释中提到的MM / DD / YYYY格式。这会将值转换为日期（如果可以），否则转换为 NULL 。然后，SQL Server将使用其默认格式将其转换回字符串。

The third argument is for the MM/DD/YYYY format mentioned in a comment. This will convert the value to a date -- if it can -- and to NULL otherwise. Then, SQL Server will use its default formats to convert back to a string.

注意：如果执行此操作，请确保备份了表，所以不要不会丢失列中的信息！

NOTE: If you do this, be sure you back up the table, so you don't lost the information in the column!

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

Then, your code should work:

ALTER TABLE Leads ALTER COLUMN [Created Date] Date;

您可以使用以下方式找到流氓值：

You can find the rogue values by using:

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

这篇关于SQL Server-将VarChar转换为ALTER TABLE中的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！