create table tb(id int, time varchar(10))insert into tb values(1, '00:00:00')
insert into tb values(2, '00:00:00')
insert into tb values(3, '00:00:00')
insert into tb values(4, '00:00:00')update tb
set time = '00:00:23'
where id = 1select * from tb drop table tb/*
id time
----------- ----------
1 00:00:23
2 00:00:00
3 00:00:00
4 00:00:00(所影响的行数为 4 行)
*/
insert into tb values(2, '00:00:00')
insert into tb values(3, '00:00:00')
insert into tb values(4, '00:00:00')update tb
set time = '00:00:23'
where id = 1select * from tb drop table tb/*
id time
----------- ----------
1 00:00:23
2 00:00:00
3 00:00:00
4 00:00:00(所影响的行数为 4 行)
*/
insert into tb values(2, '00:02:54')
insert into tb values(3, '00:00:00')
insert into tb values(4, '00:00:00')SELECT * ,
累加=right(convert(varchar(19),dateadd(second,cast(substring(time,4,2) as int) * 60 + cast(right(time,2) as int) , cast((SELECT TOP 1 time FROM TB WHERE id>A.id order by id) as datetime)),120),8)
FROM TB Adrop table tb/*
id time 累加
----------- ---------- ----------------
1 00:00:23 00:03:17
2 00:02:54 00:02:54
3 00:00:00 00:00:00
4 00:00:00 NULL(所影响的行数为 4 行)
*/
insert into tb values(2, '00:02:54')
insert into tb values(3, '00:01:11')
insert into tb values(4, '00:03:15')SELECT * ,
累加=right(convert(varchar(19),dateadd(second,cast(substring(time,4,2) as int) * 60 + cast(right(time,2) as int) , cast((SELECT TOP 1 time FROM TB WHERE id>A.id order by id) as datetime)),120),8)
FROM TB Adrop table tb/*
id time 累加
----------- ---------- ----------------
1 00:00:23 00:03:17
2 00:02:54 00:04:05
3 00:01:11 00:04:26
4 00:03:15 NULL(所影响的行数为 4 行)*/
select dateadd(second,151,'2007-08-24 00:00:23')
declare @t1 datetime,@t2 datetime
set @t1='00:00:23'
set @t2='00:02:54'
select convert(char(8),@t1+@t2,114)
你在联机帮助里看一下dateadd的用法就知道怎么处理了!
select dateadd(second,151,'2007-08-24 00:00:23')
00:00:23只是表示23秒,是一个段时间,不可能有年月
(
ID INT,
Time CHAR(8)
)
GO
INSERT INTO info VALUES(1, '00:00:00')
INSERT INTO info VALUES(2, '00:00:00')
INSERT INTO info VALUES(3, '00:00:00')
INSERT INTO info VALUES(4, '00:00:00')
GO
SELECT * FROM info
GO
/*结果
ID Time
----------- --------
1 00:00:00
2 00:00:00
3 00:00:00
4 00:00:00(4 行受影响)
*/--如果要把ID为1的Time更改为00:23(23秒)如何做
UPDATE info SET Time = '00:00:23' WHERE ID = 1
/*结果
ID Time
----------- --------
1 00:00:23
2 00:00:00
3 00:00:00
4 00:00:00(4 行受影响)
*/--如果再有一个时间段02:54(两分五四秒),和原来的00:23累加
CREATE FUNCTION dbo.addTime(@time1 VARCHAR(8), @time2 VARCHAR(8))--参数格式为00:00:00,取值范围00:00:00~23:59:59
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @time_hh INT,--时
@time_mi INT,--分
@time_ss INT,--秒
@time VARCHAR(8) --累加时间
SET @time_ss = CAST(RIGHT(@time1, 2) AS INT) + CAST(RIGHT(@time2, 2) AS INT)
SET @time_mi = CAST(RIGHT(LEFT(@time1, 5), 2) AS INT) + CAST(RIGHT(LEFT(@time2, 5), 2) AS INT)
SET @time_hh = CAST(LEFT(@time1, 2) AS INT) + CAST(LEFT(@time2, 2) AS INT) --将时间修正到正常范围
IF @time_ss >= 60--超过60秒
BEGIN
SET @time_ss = @time_ss - 60
SET @time_mi = @time_mi + 1
END
IF @time_mi >= 60--超过60分
BEGIN
SET @time_mi = @time_mi - 60
SET @time_hh = @time_hh + 1
END
IF @time_hh >= 24--超过24时
BEGIN
SET @time_hh = @time_hh - 24
END --格式化时间
SET @time = RIGHT('0' + CAST(@time_hh AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(@time_mi AS VARCHAR), 2) + ':' +
RIGHT('0' + CAST(@time_ss AS VARCHAR), 2) RETURN @time
ENDUPDATE info SET Time = dbo.addTime('00:00:23', '00:02:54') WHERE ID = 2
/*结果
ID Time
----------- --------
1 00:00:23
2 00:03:17
3 00:00:00
4 00:00:00(4 行受影响)
*/