将“nvarchar”列的数据类型转换为“DateTime”

Question

请问如何在SQL Server中将列数据类型从 "nvarchar" 格式转换为 "datetime"？

示例：

1. 01-06-2020 12:00:00 AM
2. 6-17-2020 12:00:00 AM

我已尝试以下查询：

ALTER TABLE MyTable ALTER COLUMN UpdatedDate datetime

但是，出现了以下错误：

nvarchar 数据类型向 datetime 数据类型的转换结果超出范围。

- Mohanraj Periyannan

---

你的值不正确。 - shawnt00

Answer 1

现在你已经看到这个问题有多么棘手了，我希望你已经学到了永远不要将DateTime值存储为字符串。你应该始终使用最合适的可用数据类型。
不过不用担心，这是一个非常普遍的错误，Aaron Bertrand 写了一篇关于它（或者更普遍的问题）的博客文章 - 名为Bad habits to kick : choosing the wrong data type。

那么这就是你处理这种情况的方法： (TL;DR; 在 Rextester 上有实时演示)

首先，创建并填充示例表格（请在您未来的问题中省略此步骤）：

CREATE TABLE MyTable (
    Id int identity(1,1),
    UpdatedDate nvarchar(30)
);


INSERT INTO MyTable(UpdatedDate) VALUES
('01-06-2020 12:00:00 AM'),
('6-17-2020 12:00:00 AM'),
('02-30-2020 12:00:00 AM'), -- Bad data
('1-06-2020 12:00:00 ZM'),  -- Bad data
('01-06-2020 13:00:00 PM'),
('13-06-2020 13:00:00 PM'),  -- Bad data
('01-06-2020 1:00:00 PM'),
('1-6-2020 1:00:00 AM'),
('Not a date at all...');  -- Bad data

请注意，我已添加了一些包含无法转换为 DateTime 值的数据行。

然后，第一步是获取一个值列表，其中将无法进行转换。

SELECT Id, UpdatedDate
FROM dbo.MyTable
WHERE TRY_CONVERT(DateTime2, UpdatedDate, 110) IS NULL;

这将返回以下记录集（针对此示例数据）：

Id  UpdatedDate
3   02-30-2020 12:00:00 AM
4   1-06-2020 12:00:00 ZM
6   13-06-2020 13:00:00 PM
9   Not a date at all...

现在您已经有了列表，您可以决定是手动修复它们、删除记录，还是简单地忽略这些值，在这些行中将新的UpdatedDate列保持为NULL。

完成后，您需要向表中添加一个新列，使用临时名称。我建议使用DateTime2而不是DateTime——因为它是更好的数据类型。

ALTER TABLE dbo.MyTable 
ADD UpdatedDate_New DateTime2 NULL;

接下来，您需要填充此列（在此假设您已经决定将其保留为空，如果原始列的数据无法转换为DateTime2）。

UPDATE dbo.MyTable
SET  UpdatedDate_New = TRY_CONVERT(DateTime2, UpdatedDate, 110);

现在，从表中删除旧列：

ALTER TABLE dbo.MyTable 
DROP COLUMN UpdatedDate;

最后，重新命名新列：

EXEC sp_Rename 'dbo.MyTable.UpdatedDate_New', 'UpdatedDate', 'COLUMN';

验证结果：

SELECT *
FROM dbo.MyTable

结果：

Id  UpdatedDate
1   06.01.2020 00:00:00
2   17.06.2020 00:00:00
3   NULL
4   NULL
5   06.01.2020 13:00:00
6   NULL
7   06.01.2020 13:00:00
8   06.01.2020 01:00:00
9   NULL

在这个答案中有一个问题我还没有回答，那就是原始列的依赖关系。您应该检查什么依赖于原始列，因为有时您需要根据您更改的列进行代码调整。

- Zohar Peled

---

很高兴能帮忙 :-) - Zohar Peled

Answer 2

这取决于您的dateformat设置：

create table t(dt varchar(10));

insert t(dt) select '20/7/2020';

set dateformat mdy;

alter table t alter column dt datetime ;
--error

set dateformat dmy;

alter table t alter column dt datetime;
-- ok, because the format of the dates in the table is dmy

对于您的情况，您的值采用月/日/年的格式。因此，您需要执行set dateformat mdy

- allmhuran

---

当然可以。但是这个问题是关于如何从一个格式转换到另一个格式，而不是关于如何进行数据清洗的。我会提取包含无效日期的数据，将其交给业务部门进行更正，然后再进行列转换。我们不能简单地假设我们可以丢弃这些数据。 - allmhuran

如果你仔细阅读，我已经写明基本上有两个选择：要么手动修复损坏的数据，要么忽略并丢弃它（可以是整行或部分）。当然，更好的选择是修复损坏的数据，但这并不总是可行的（想象一下一个只有50万条记录的小表，其中1%的记录具有不正确的updatedDate值。那就需要手动筛选5000行，这真的不是一个可爱的选择，对吧？） - Zohar Peled

无论如何，我的观点是你的答案可能仍然不能解决 OP 的问题，因为 DateFormat 可能根本不是问题所在。问题提供的样本数据不足以得出任何结论-所以我在写答案时采取了最坏的情况。 - Zohar Peled

这仍然是你超出了范围。这个问题的范围是如何进行转换，以及为什么在这种情况下似乎失败了。我明确表示数据清洗不在此范围内。你的批评相当愚蠢，因为你的解决方案也有完全相同的问题：你必须对日期格式做出完全相同的假设。你的 try_convert 表达式假定了一个格式。你怎么知道 '1/2/2020' 是二月的第一天还是一月的第二天？ - allmhuran

我假设（也许是错误的）提供给OP的示例数据是有效日期（否则，如果OP知道数据无效，问题应该是“如何修复坏数据”）。基于这个假设，我得出了最有可能的格式。 - Zohar Peled

Answer 3

你必须使用月/日/年的格式。就像下面的例子一样：

IF OBJECT_ID('TABLE1') IS NOT NULL
    DROP TABLE TABLE1
GO
CREATE TABLE TABLE1 (myDate NVARCHAR(128))
GO
INSERT TABLE1 VALUES('01-06-2020 12:00:00 AM'), ('6-17-2020 12:00:00 AM')
GO
SET DATEFORMAT MDY;
GO
ALTER TABLE TABLE1 ALTER COLUMN myDate DATETIME
GO
SELECT * FROM TABLE1

在这个数据集中，它将会正常工作。

- iman kazemi

---

这是假设所有的值都是有效的。但是，对于 nvarchar 数据类型来说，确保这一点非常困难。有什么办法阻止用户输入 13-35-1000 Ba:na:na AM 这样的值呢？ - Zohar Peled