SQL Server 2008：Varchar到日期时间转换

问题描述：

我有一个datetime列ArrivalDateTime它存储为varchar值。SQL Server 2008：Varchar到日期时间转换

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

我可以得到日期时间格式的日期部分如下。

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

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

我尝试了下面的查询来获取时间部分。

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

但是，这将引发一个错误：

The data types date and varchar are incompatible in the add operator

我怎样才能获得日期和时间部分datetime格式？

阅读阿龙贝特朗的[坏习惯踢：选择了错误的数据类型（http://sqlblog.com/blogs/aaron_bertrand /archive/2009/10/12/bad-habits-to-kick-using-the-wrong-data-type.aspx）文章。 –

感谢队友的指针。不幸的是，我必须利用其他人创建的列。我现在不能改变它的数据类型。 – turbo88

@ turbo88你不能简单地忽略这个设计错误，因为它影响索引，查询和性能。通过使用任意字符串而不是日期，可以禁用所有与日期相关的查询优化。在查询之前，您必须将任何日期标准转换为此任意格式。没有解析，您将无法使用任何与日期相关的功能。最后，当查询引擎可以使用包含日期列的基于集合的操作或索引时，它必须处理并传递每个结果行。这会导致巨大的性能损失 –

答

先获取日期分量并将其转换为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 s到获得：

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' 
    )

辉煌。有什么办法可以忽略毫秒吗？ – turbo88

答

试试这个，

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

它在2个独立的列中显示日期和时间。我希望他们在单列中。 – turbo88

答

试试这个

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))

答

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

SQL Server 2008：Varchar到日期时间转换

相关推荐