将“MyDateTime字段”减去“1”。比如,“2008101801”表示的是“2008年10月18日01时”,现在想将“MyDateTime字段”都减去一小时。也就是“2008101801”变为“2008101800”,“2008101802”变为“2008101801”,“2008101803”变为“2008101802”, 这样的处理语句应该怎样写???
将“MyDateTime字段”减去“1”。比如,“2008101801”表示的是“2008年10月18日01时”,现在想将“MyDateTime字段”都减去一小时。也就是“2008101801”变为“2008101800”,“2008101802”变为“2008101801”,“2008101803”变为“2008101802”, 这样的处理语句应该怎样写???
DROP TABLE test1 ;
CREATE TABLE test1
(
ID INT,
MyDateTime NVARCHAR(22)
)
INSERT INTO test1
SELECT 1,'2008101800'
UNION
SELECT 2,'2008101801'
UNION
SELECT 3,'2008101802'
UNION
SELECT 4,'2008101803'IF NOT OBJECT_ID('#a') IS NOT NULL
DROP TABLE #a
SELECT ID ,
MyDateTime ,
DATEADD(HOUR
, CONVERT(INT, RIGHT(MyDateTime, 2)),CONVERT(DATETIME, LEFT(MyDateTime, 8))
) AS 'datetime' ,
CONVERT(NVARCHAR(30)
, DATEADD(HOUR, -1,DATEADD(HOUR,CONVERT(INT, RIGHT(MyDateTime, 2)),CONVERT(DATETIME, LEFT(MyDateTime,8))))
, 20) AS 'reduce'
INTO #a
FROM test1SELECT * FROM #aSELECT ID,
MyDateTime,
LEFT(REPLACE(REPLACE(reduce,'-',''),' ',''),10)
FROM #a
set mydatetime=cast(cast(mydatetime)as int)-1 as varchar(20))
go