请教，Sql字符串转换日期类型

Question

YYYYMMDDHHmmss格式的字符串无法直接转化成DATETIME
YYYYMMDD的却可以，如果你不需要时分的计算，可以直接LEFT(aTime,8)和bTime相加减
如果需要用到时分，那么要复杂一点

Answer 1

WITH table1(atime,btime) AS (
    SELECT '20141215022118', GetDate()
)
SELECT Convert(datetime,
               LEFT(atime,4)+'-'+SUBSTRING(atime,5,2)+'-'+SUBSTRING(atime,7,2)+' '+
               SUBSTRING(atime,9,2)+':'+SUBSTRING(atime,11,2)+':'+SUBSTRING(atime,13,2),
               120) atime,
       btime
  FROM table1
atime                   btime
----------------------- -----------------------
2014-12-15 02:21:18.000 2015-01-13 15:15:34.950

Answer 2

declare @i varchar(50)
set @i=20141215022118
print cast(left(@i,4)+'-'+substring(@i,5,2)+'-'+substring(@i,7,2)+' '
+substring(@i,9,2)+':'+substring(@i,11,2)+':'+substring(@i,13,2) as datetime)