怎样把bigint型“20040302215041”的数据转换成datetime型的"2004-3-2 21:50:41"

Question

select substring(FieldName,1,4)+'-'+substring(FieldName,5,2)+'-'+substring(FieldName,7,2)+' '+substring(FieldName,9,2)+':'+substring(FieldName,11,2)+':'+substring(FieldName,13,2)

Answer 1

declare @fieldname varchar(50)
set @fieldname=cast(20040302215041 as varchar(50))select cast(substring(@fieldname,1,4)+'-'+substring(@fieldname,5,2)+'-'+substring(@fieldname,7,2)+' '+substring(@fieldname,9,2)+':'+substring(@fieldname,11,2)+':'+substring(@fieldname,13,2) as datetime)
--结果
------------------------------------------------------ 
2004-03-02 21:50:41.000(1 row(s) affected)

Answer 2

declare @d bigint
set @d=20040302215041select cast(left(@d,8)+' '+stuff(stuff(right(@d,6),3,0,':'),6,0,':') as datetime)

Answer 3

declare @d bigint
set @d=20040302215041select cast(left(@d,8)+' '+stuff(stuff(right(@d,6),3,0,':'),6,0,':') as datetime)/*--测试结果
------------------------------------------------------ 
2004-03-02 21:50:41.000（所影响的行数为 1 行）--*/