将 nvarchar 转换为日期时间 [英] Cast/Convert nvarchar to datetime
问题描述
我有桌子:
id timestamp
1 31.10.2020 16:32:11
2 09.09.2020 09:15:49
3 22.04.2020 02:48:15
表中有大量这些数据.列时间戳是数据类型nvarchar".而且我需要按日期对数据进行排序或在 WHERE 子句中使用按日期进行约束,因此我需要将时间戳"转换为时间戳".到日期时间.但我不能.我试过转换和转换,但还是失败了.
Table have huge amount these data. Column timestamp is data type "nvarchar". And i need to sort datas by date or use in clauses WHERE for constraint by date, so i need to convert "timestamp" to datetime. But I can't. I tried convert and cast but still failed.
有什么建议吗?
谢谢.
推荐答案
修复您的设计,并将列更改为日期和时间数据类型.考虑到您的数据精确到 1 秒,datetime2(0)
在这里似乎很合适.首先,我们需要更改格式"nvarchar
值转换为 ISO 格式.我们将使用 ISO8601 格式 (yyyy-mm-ddThh:mi:ss.mmm),因为它是明确的:
Fix your design, and change the column to a date and time data type. Considering your data is accurate to 1 second, a datetime2(0)
seems appropriate here. First we need to change the "format" of the nvarchar
value to an ISO format. We're going to use the ISO8601 format (yyyy-mm-ddThh:mi:ss.mmm) as it's unambiguous:
UPDATE dbo.YourTable
SET [timestamp] = CONVERT(nvarchar(20),TRY_CONVERT(datetime2(0), [timestamp], 4), 126);
现在您可以ALTER
表格并将数据类型更改为datetime2(0)
:
Now you can ALTER
the table and change the data type to a datetime2(0)
:
ALTER TABLE dbo.YourTable ALTER COLUMN [timestamp] datetime2(0) NULL;
我还建议为您的列使用与 timestamp
不同的名称.timestamp
是 rowversion
的弃用同义词,因此它的使用会使它变得相当混乱;特别是 rowversion
不是日期和时间值,而是 binary
值,并且不能转换为日期和时间数据类型.
I also recommend using a different name than timestamp
for your column. timestamp
is a deprecated synonym for rowversion
, so it's use can make it quite confusing; especially as rowversion
is not a date and time value but a binary
value and cannot be converted to a date and time data type.
这篇关于将 nvarchar 转换为日期时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!