# MSsqlServer数据库里如何计算得到08:43这种格式的时间数据

-1

select ta.[user],(DATEDIFF(mi,ta.time,tb.time)) from (select * from T1 where T1.operate='Login') as ta inner join (select * from T1 where T1.operate='Logout') as tb

on ta.[user]=tb.[user]

---------------------------------

select ta.[user], cast(datediff(hour, ta.time, tb.time) as varchar) + ':' + cast(DATEDIFF(MINUTE, ta.time, tb.time) as varchar)
from
(select [user], [time] from T1 where [operate] = 'login') as ta
inner join
(select [user], [time] from T1 where [operate] = 'logout') as tb
on ta.[user] = tb.[user];

datediff的结果是总在线的分钟或者秒数。

0
select convert(varchar, convert(datetime, datediff(SS, '2010-08-10 08:00:59', '2010-08-10 16:09:01')/convert(decimal,86400)), 8)

--08:08:02

0

-1
DECLARE @st DATETIME=GETDATE(),@et DATETIME='2013-10-13 23:55:12'

SELECT CONVERT(VARCHAR(5),@et-@st,114)

0

@荆棘人: 用datepart函数可以取到时间的时分

0

DECLARE  @data table ([user] varchar(200), operate varchar(200), [time] datetime)

INSERT into @data
('LiMing', 'Logout', '2010-10-24 16:14'),
('WangYi', 'Logout', '2010-10-24 16:44')

select t1.[user],
CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, (select max([time]) from @data t where operate = 'Login' And t.[user] = t1.[user]), (select max([time]) from @data t where operate = 'Logout' And t.[user] = t1.[user])), 0), 114)
from @data t1
group by t1.[user]

gunsmoke | 园豆：3592 (老鸟四级) | 2013-10-16 12:49

您需要登录以后才能回答，未注册用户请先注册