本文介绍了在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更多推荐
在sql server中将datetime转换为yyyymmddhhmmss
发布评论