SQL Server 2008：将Varchar转换为Datetime

Question

我有一个名为 ArrivalDateTime 的 datetime 列，它以 varchar 值的形式存储。

假设该值为 20161212093256，我希望输出为 2016-12-12 09:32:56。

我可以通过以下方式获取日期部分的 datetime 格式。

SELECT 
    CONVERT(DATETIME2(0), LEFT('20161212093256', 8))

这将返回输出为2016-12-15 00:00:00。

我尝试了以下查询，以获得时间部分。

SELECT 
    CONVERT(DATE, LEFT('20161212093256', 8)) + ' ' + 
    CONVERT(TIME, RIGHT('20161212093256', 6))

但是这会导致错误：

数据类型 date 和 varchar 在加法运算符中不兼容

我如何以 datetime 格式获取日期和时间部分？

- turbo88

---

阅读Aaron Bertrand的《Bad habits to kick : choosing the wrong data type》文章。 - Zohar Peled

谢谢你的指针。不幸的是，我必须使用别人创建的列。现在我无法更改它的数据类型。 - turbo88

@turbo88，你不能简单地忽略这个设计缺陷，因为它影响了索引、查询和性能。使用任意字符串代替日期，将禁用所有与日期相关的查询优化。你必须在查询之前将任何日期标准转换为这种任意格式。你将无法使用任何日期相关函数而不进行解析。最后，查询引擎将不得不处理并传递每一行结果，而它本可以使用基于集合的操作或包括日期列的索引。这将导致巨大的性能惩罚。 - Panagiotis Kanavos

@turbo88 考虑添加一个持久化计算列，解析字符串列并返回实际的日期时间。您可以将索引应用于持久化计算列，只有在文本列更改时才会执行解析。 - Panagiotis Kanavos

Answer 1

首先获取日期组件并将其转换为DATETIME，然后获取时间组件并将其转换为DATETIME。最后，相加这两个结果：

SELECT 
    CONVERT(DATETIME,LEFT('20161212093256', 8)) +
    CONVERT(DATETIME,
        LEFT(RIGHT('20161212093256', 6), 2) + ':' +
        SUBSTRING(RIGHT('20161212093256', 6), 3, 2) + ':' +
        RIGHT(RIGHT('20161212093256', 6), 2)
    )

---

进一步解释，第一次转换的结果是日期组件:

2016-12-12 00:00:00.000

第二个转换是时间组件，但当你将其转换为DATETIME时，它会将其添加到0日期或'1900-01-01'，因此结果如下：

1900-01-01 09:32:56.000

然后，您将两个 DATETIME 相加得到：

2016-12-12 09:32:56.000

为了去掉“ms”组件：

SELECT 
    CONVERT(DATETIME,LEFT('20161212093256', 8)) +
    CONVERT(DATETIME,
        LEFT(RIGHT('20161212093256', 6), 2) + ':' +
        SUBSTRING(RIGHT('20161212093256', 6), 3, 2) + ':00'
    )

- Felix Pamittan

---

太棒了。有没有办法可以忽略毫秒？ - turbo88

Answer 2

试试这个，

DECLARE @V_STR VARCHAR(20) = '20161212093256'
SELECT  CONVERT(SMALLDATETIME,LEFT(@V_STR,8) +' '+      --date
                SUBSTRING(@V_STR,9,2)+':'+              --hour
                SUBSTRING(@V_STR,11,2)+':'+             --minute
                SUBSTRING(@V_STR,13,2)) AS  DATE_TIME   --second

- Abdul Rasheed

---

它在两个单独的列中显示日期和时间。我想要它们在一个单独的列中。 - turbo88

Answer 3

试试这个

select concat(CONVERT(DATE, LEFT('20161212093256', 8)) , ' ' , CONVERT(TIME,  substring(RIGHT('20161212093256', 6),1,2)+ ':'  + substring(RIGHT('20161212093256', 4),1,2) + ':' +RIGHT('20161212093256', 2)))

上面将显示带有毫秒的时间，下面将不显示毫秒

select concat(CONVERT(DATE, LEFT('20161212093256', 8)) , ' ' , substring(RIGHT('20161212093256', 6),1,2)+ ':'  + substring(RIGHT('20161212093256', 4),1,2) + ':' +RIGHT('20161212093256', 2))

- Anil

Answer 4

SELECT STUFF(STUFF(STUFF(STUFF(STUFF('20161212093256', 5, 0, '-'), 8, 0, '-'), 11, 0, ' '), 14, 0, ':'), 17, 0, ':')

- Sean Pearce