34,576
社区成员
发帖
与我相关
我的任务
分享
declare @t1 table
(
id int identity(1,1),
f1 time,
f2 time,
f3 time,
f4 date
)
insert @t1 select '1:10','1:20','1:30','2008-1-3'
union all select '4:20','6:18','9:25','2008-1-5'
union all select '2:05','2:23','2:40','2008-1-4'
select
id,
f4,
[f2-f1] = datediff(minute,f1,f2),
[f3-f2] = datediff(minute,f2,f3)
from @t1
order by f4
CREATE TABLE tb(id INT, f1 VARCHAR(10),f2 VARCHAR(10),f3 VARCHAR(10),f4 VARCHAR(10))
INSERT tb SELECT 1 , '1:10' , '1:20' , '1:30' , '2008-1-3'
UNION ALL SELECT 2 , '4:20' , '6:18' , '9:25' , '2008-1-5'
UNION ALL SELECT 3 , '2:05' , '2:23' , '2:40' , '2008-1-4'
GO
select id , f4,
'f2-f1' = datediff(mi,cast(f4+' '+f1+':00' as datetime),cast(f4+' '+f2+':00' as datetime)),
'f3-f2' = datediff(mi,cast(f4+' '+f2+':00' as datetime),cast(f4+' '+f3+':00' as datetime))
from tb
drop table tb
/*
id f4 f2-f1 f3-f2
----------- ---------- ----------- -----------
1 2008-1-3 10 10
2 2008-1-5 118 187
3 2008-1-4 18 17
(所影响的行数为 3 行)
*/
DECLARE @t TABLE(id INT,f1 NVARCHAR(5),f2 NVARCHAR(5),f3 NVARCHAR(5),f4 DATETIME)
INSERT INTO @t
SELECT 1,'1:10','1:20','1:30','2008-1-3 ' UNION ALL
SELECT 2,'4:20','6:18','9:25',' 2008-1-4 ' UNION ALL
SELECT 3,'2:05','2:23','2:40',' 2008-1-5 '
SELECT id,f4,(LEFT(f2,LEN(f2)-3)*60+RIGHT(f2,2))-(LEFT(f1,LEN(f1)-3)*60+RIGHT(f1,2))AS 'f2-f1',
(LEFT(f3,LEN(f3)-3)*60+RIGHT(f3,2))-(LEFT(f2,LEN(f2)-3)*60+RIGHT(f2,2))AS 'f3-f2'
FROM @t
ORDER BY f4
(3 row(s) affected)
id f4 f2-f1 f3-f2
----------- ------------------------------------------------------ ----------- -----------
1 2008-01-03 00:00:00.000 10 10
2 2008-01-04 00:00:00.000 118 187
3 2008-01-05 00:00:00.000 18 17
(3 row(s) affected)
CREATE TABLE t1(id INT, f1 VARCHAR(10),f2 VARCHAR(10),f3 VARCHAR(10),f4 VARCHAR(10))
GO
INSERT t1 SELECT 1 ,'1:10', '1:20' , '1:30' , '2008-1-3'
UNION ALL SELECT 2 , '4:20' , '6:18' , '9:25' , '2008-1-5'
UNION ALL SELECT 3, '2:05' , '2:23', '2:40', '2008-1-4'
GO
SELECT id,f4,
[f2-f1]=DATEDIFF(mi, f4 + ' ' + f1 + ':00.000',f4 + ' ' + f2 + ':00.000'),
[f3-f2]=DATEDIFF(mi, f4 + ' ' + f2 + ':00.000',f4 + ' ' + f3 + ':00.000')
FROM t1
GO
/*
1 2008-1-3 10 10
2 2008-1-5 118 187
3 2008-1-4 18 17
*/
DROP TABLE t1
GO
declare @t table(id int,f1 varchar(10),
f2 varchar(10),f3 varchar(10),f4 datetime
)
insert into @t select
1, '1:10', '1:20' , '1:30', '2008-1-3'
union all select
2, '4:20' , '6:18' , '9:25' , '2008-1-5'
union all select
3, '2:05' , '2:23' , '2:40' , '2008-1-4'
select id,DATEDIFF(mi,f4+' '+f1,f4+' '+f2) as [f2-f1],
DATEDIFF (mi,f4+' '+f2,f4+' '+f3) as [f3-f2],
f4
from @t
order by f4
select
id,
f4,
[f2-f1] = abs(datediff(minute,convert(varchar(10),f4,120) + ' ' + convert(varchar(10),f2,120)
,convert(varchar(10),f4,120) + ' ' + convert(varchar(10),f1,120))),
[f3-f2] = abs(datediff(minute,convert(varchar(10),f4,120) + ' ' + convert(varchar(10),f3,120)
,convert(varchar(10),f4,120) + ' ' + convert(varchar(10),f2,120)))
from @t1
order by f4
declare @t1 table
(
id int identity(1,1),
f1 time,
f2 time,
f3 time,
f4 date
)
insert @t1 select '1:10','1:20','1:30','2008-1-3'
union all select '4:20','6:18','9:25','2008-1-5'
union all select '2:05','2:23','2:40','2008-1-4'
select
id,
f4,
[f2-f1] = datediff(minute,convert(varchar(10),f4,120) + ' ' + convert(varchar(10),f1,120)
,convert(varchar(10),f4,120) + ' ' + convert(varchar(10),f2,120)),
[f3-f2] = datediff(minute,convert(varchar(10),f4,120) + ' ' + convert(varchar(10),f2,120)
,convert(varchar(10),f4,120) + ' ' + convert(varchar(10),f3,120))
from @t1
order by f4