请问如何在SQL Server中将列数据类型从 "nvarchar
" 格式转换为 "datetime
"?
示例:
01-06-2020 12:00:00 AM
6-17-2020 12:00:00 AM
我已尝试以下查询:
ALTER TABLE MyTable ALTER COLUMN UpdatedDate datetime
但是,出现了以下错误:
nvarchar 数据类型向 datetime 数据类型的转换结果超出范围。
请问如何在SQL Server中将列数据类型从 "nvarchar
" 格式转换为 "datetime
"?
示例:
01-06-2020 12:00:00 AM
6-17-2020 12:00:00 AM
我已尝试以下查询:
ALTER TABLE MyTable ALTER COLUMN UpdatedDate datetime
但是,出现了以下错误:
nvarchar 数据类型向 datetime 数据类型的转换结果超出范围。
现在你已经看到这个问题有多么棘手了,我希望你已经学到了永远不要将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
在这个答案中有一个问题我还没有回答,那就是原始列的依赖关系。您应该检查什么依赖于原始列,因为有时您需要根据您更改的列进行代码调整。
这取决于您的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
try_convert
表达式假定了一个格式。你怎么知道 '1/2/2020' 是二月的第一天还是一月的第二天? - allmhuran你必须使用月/日/年的格式。就像下面的例子一样:
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
nvarchar
数据类型来说,确保这一点非常困难。有什么办法阻止用户输入 13-35-1000 Ba:na:na AM
这样的值呢? - Zohar Peled