在 SQL 中将包含毫秒的 varchar 转换为 datetime

Question

我有一个列abc varchar(100)，其中包含类似于2011-09-26 16:36:57.810000的数据。

我想将此列转换为DATETIME类型...

但是执行以下操作：

Convert(DATETIME, abc,120) 

出现以下错误：

将日期和/或时间从字符字符串转换失败。

请问有人能帮我在SQL Server 2008中将我的varchar格式转换为datetime吗？

先感谢您的帮助。

- Pink

---

字符串 2011-09-26 16:36:57.810000 无法转换为日期时间。毫秒只能是0-999。 - TechDo

但是我该如何将这列转换为DATETIME，还有其他的方法吗？ - Pink

Answer 1

您可以使用121样式，但毫秒部分只能有3位数字（例如yyyy-mm-dd hh:mi:ss.mmm(24小时制)）。

declare @abc varchar(100)='2011-09-26 16:36:57.810' 
select convert(datetime,@abc,121)

所以，在转换之前，您可以通过将varchar字段限制为23个字符来进行排序，例如：

declare @abc varchar(100)='2011-09-26 16:36:57.810000' 
select convert(datetime,convert(varchar(23),@abc),121)

或使用Left()函数获取前23个字符：

select convert(datetime,left(@abc,23),121)

尽量避免将日期以字符串形式存储。

- Kaf

---

我已经有6位数的用户数据了，所以我认为我需要拆分最后3位数字？ - Pink

是的，在转换之前，您必须确保毫秒只有3位数字。我已经更新了我的答案。 - Kaf

你还应该注意DateTime类型的精度 - 例如，.997将被正确保留，但是强制转换像.998或.999这样的值将导致该值四舍五入到最接近的秒。 - ifx

Answer 2

如果需要6位小数精度，请使用DATETIME2。

SELECT CONVERT(DATETIME2, '2016-08-09T08:08:50.358000', 126) as MSSQLDateTime2
SELECT CONVERT(DATETIME, '2016-08-09T08:08:50.358', 126) as MSSQLDateTime

- GBrian

---

抱歉，Datetime2 仅在 SQL Server 2008 R2 及以上版本可用。 - GBrian

Answer 3

假设我们有以下字符串变量：

DECLARE @d VARCHAR(100)  = '2020-04-06T04:35:07.9490051Z' -- 7 digits nanoseconds
DECLARE @d1 VARCHAR(100) = '2020-04-05T15:00:00Z'         -- simple: without nanoseconds

我使用CAST运算符找到了解决方案：

SELECT CAST(LEFT(@d,19) + 'Z' AS DATETIME)  -- outputs: 2020-04-06 04:35:07.000
SELECT CAST(LEFT(@d1,19) + 'Z' AS DATETIME) -- outputs: 2020-04-05 15:00:00.000

- Arsen Khachaturyan

Answer 4

SQL Server 只支持毫秒的 3 位小数，因此以下代码可以正常工作：

Convert(DATETIME, SUBSTRING(abc, 0, 24) ,120) 

- ifx

Answer 5

根据GBrian的回答，我想到了：

CONVERT(DATETIME, CONVERT(DATETIME2, abc, 126))

我还没有对这个与基于子字符串的解决方案进行基准测试。

- kamilk