如何使用SQL将字符串转换为不带分隔符的日期时间? [英] How to convert string to datetime without seperator using sql?

问题描述

如何将此字符串转换为Datetime

How to convert this string to Datetime

20180227105954636241

20180227105954636241

select CONVERT(datetime, '20180227105954636241', 120)

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

Conversion failed when converting date and/or time from character string.

推荐答案

您无法将这些带图案的字符串直接转换为 datetime ，而且我看不到 CONVERT 的任何用途函数用于此目的.您只需将如下所示的字符串格式化为 cast 即可:

You can't convert these patterned string to datetime directly and i don't see any use of CONVERT function for this purpose. You can just format the string like below to cast it :

DECLARE @date VARCHAR(MAX) = '20180227105954636241'
DECLARE @date1 VARCHAR(30) 

set @date1 = SUBSTRING(@date,  1, 8) + ' ' +  -- this is datepart
             SUBSTRING(@date,  9, 2) + ':' +  -- this is hour
             SUBSTRING(@date, 11, 2) + ':' +  -- this is minute
             SUBSTRING(@date, 13, 2) + '.' +  -- this is second
             SUBSTRING(@date, 15, 6)          -- this is decimal of second

select cast(@date1 as datetime2(6))  -- 6 is the decimal point of second

输出

2018-02-27 10:59:54.636241

这篇关于如何使用SQL将字符串转换为不带分隔符的日期时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！