在sql server中将datetime转换为yyyymmddhhmmss

本文介绍了在sql server中将datetime转换为yyyymmddhhmmss的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要从yyyymmddhhmmss计算当地时间，并将其作为yyyymmddhhmmss返回。我尝试过下面的工作，但是我无法摆脱月份名称。

I need to calculate the local time from yyyymmddhhmmss and return it as yyyymmddhhmmss. I have tried the below, it is working but I am not able to get rid of the month name.

Declare @VarCharDate varchar(max) Declare @VarCharDate1 varchar(max) Declare @VarCharDate2 varchar(max) --Declare set @VarCharDate = '20131020215735' --- YYYYMMDDHHMMSS --Convert set @VarCharDate1 =(select SUBSTRING(@VarCharDate,0,5) + '/' + SUBSTRING(@VarCharDate,5,2) + '/' + SUBSTRING(@VarCharDate,7,2) + ' ' + SUBSTRING(@VarCharDate,9,2) +':'+SUBSTRING(@VarCharDate,11,2) +':' + RIGHT(@VarCharDate,2)) select @VarCharDate1 --Convert to Date and Add offset set @VarCharDate2 = DATEADD(HOUR,DateDiff(HOUR, GETUTCDATE(),GETDATE()),CONVERT(DATETIME,@VarCharDate1,20)) select @VarCharDate2 -- Now we need to revert it to YYYYMMDDhhmmss --Tried this but month name still coming Select convert(datetime, @VarCharDate2, 120)

推荐答案

尝试这个 -

Declare @VarCharDate varchar(max) Declare @VarCharDate1 varchar(max) Declare @VarCharDate2 varchar(max) --Declare set @VarCharDate = '20131020215735' --- YYYYMMDDHHMMSS --Convert set @VarCharDate1 =(select SUBSTRING(@VarCharDate,0,5) + '/' + SUBSTRING(@VarCharDate,5,2) + '/' + SUBSTRING(@VarCharDate,7,2) + ' ' + SUBSTRING(@VarCharDate,9,2) +':'+SUBSTRING(@VarCharDate,11,2) +':' + RIGHT(@VarCharDate,2)) select @VarCharDate1 --Convert to Date and Add offset set @VarCharDate2 = DATEADD(HOUR,DateDiff(HOUR, GETUTCDATE(),GETDATE()),CONVERT(DATETIME,@VarCharDate1,20)) select @VarCharDate2 SELECT REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19), CONVERT(DATETIME, @VarCharDate2, 112), 126), '-', ''), 'T', ''), ':', '') [date]

返回 -

date 20131021035700