SQL时间相减？？？在线等！

[sunboy_163]

查询停电时间：
select top 1 rd_time from reading where pk_id=2546 and rd_ac='断电' order by rd_id
查询结果：2009-10-10 12:15:16

查询停电终于时间：
select top 1 rd_time from reading where pk_id=2546 and rd_ac='断电' order by rd_id desc
查询结果：2009-10-10 12:22:28


如何写一条SQL语句？计算一共停了多长时间电？应该是：（2009-10-10 12:22:28-2009-10-10 12:15:16)=7分钟的样子？

[sgtzzc]

[Quote=引用 3 楼 fredrickhu 的回复:]
SQL codeselectdatediff(mi,a.rd_time,b.rd_time)from
(selecttop1 rd_timefrom readingwhere pk_id=2546and rd_ac='断电'orderby rd_id )a,
(selecttop1 rd_timefrom readingwhere pk_id=2546and rd_ac='断电'orde¡­
[/Quote]

a表和b表没有rd_id字段，两个表的结果都只有一个值，没必要关联

[dawugui]

select datediff(mi,m.rd_time,n.rd_time) from 
(select top 1 rd_time from reading where pk_id=2546 and rd_ac='断电' order by rd_id ) m,
(select top 1 rd_time from reading where pk_id=2546 and rd_ac='断电' order by rd_id desc )n

[bancxc]

[Quote=引用 6 楼 luoyoumou 的回复:]
SQL codeselectdatediff(mi,(selecttop1 rd_timefrom readingwhere pk_id=2546and rd_ac='断电'orderby rd_id),
(selecttop1 rd_timefrom readingwhere pk_id=2546and rd_ac='断电'orderby rd_iddesc )as'断电时长';
[/Quote]
0

[luoyoumou]

--闲着没事：瞎发发
select datediff(mi,getdate()-1,getdate());

select datediff(min,(select top 1 rd_time from reading where pk_id=2546 and rd_ac='断电' order by rd_id),
					(select top 1 rd_time from reading where pk_id=2546 and rd_ac='断电' order by rd_id desc)) as '断电时长';

[ilmarezhou]

用Datediff函数啊...

[bancxc]

--我也来个
select DateDiff(minute,min(rd_time),max(rd_time)) as 断电时长
from 
(
select top 1 rd_time from reading where pk_id=2546 and rd_ac='断电' order by rd_id 
union all
select top 1 rd_time from reading where pk_id=2546 and rd_ac='断电' order by rd_id desc 
) t

[ks_reny]

[Quote=引用 11 楼 ilmarezhou 的回复:]
用Datediff函数啊...
[/Quote]
....

[sunboy_163]

select datediff(mi,m.rd_time,n.rd_time) from
(select top 1 rd_time from reading where pk_id=2446 and rd_ac='断电' order by rd_id ) m,
(select top 1 rd_time from reading where pk_id=2446 and rd_ac='断电' order by rd_id desc )n

如果是相隔几天？就算出这几天分钟的总合？

[luoyoumou]

---楼主：还有啥新花样，说明白点............

[--小F--]

[Quote=引用 7 楼 sgtzzc 的回复:]
引用 3 楼 fredrickhu 的回复:
SQL codeselectdatediff(mi,a.rd_time,b.rd_time)from
  (selecttop1 rd_timefrom readingwhere pk_id=2546and rd_ac='断电'orderby rd_id )a,
  (selecttop1 rd_timefrom readingwhere pk_id=2546and rd_ac='断电'orde¡­


a表和b表没有rd_id字段，两个表的结果都只有一个值，没必要关联
[/Quote]

恩 谢谢 没注意

[navy887]

datediff

[xiequan2]

[Quote=引用 14 楼 sunboy_163 的回复:]
select datediff(mi,m.rd_time,n.rd_time) from
(select top 1 rd_time from reading where pk_id=2446 and rd_ac='断电' order by rd_id ) m,
(select top 1 rd_time from reading where pk_id=2446 and rd_ac='断电' order by rd_id desc )n

如果是相隔几天？就算出这几天分钟的总合？
[/Quote]

declare @s datetime

set @s=getdate()
declare @e datetime
set @e='2009-10-18'
select ltrim(datediff(s,@s,@e)/60)+':'+ltrim(datediff(s,@s,@e)%60) --得到的是两个时间相隔的分钟和秒

/*

-------------------------
737:9
*/

[sunboy_163]

显示格式能不能以小时：分钟表示？

[xiequan2]

declare @starttime as datetime
declare @endtime as datetime
set @starttime = '2008-3-18 17:07:21' 
set @endtime = '2008-3-16 17:08:23'

select right('00'+ cast(cast(datediff(ss ,@starttime,@endtime) / 3600 as int) as varchar),2) + ':' + 
       right('00'+ cast(cast(datediff(ss ,@starttime,@endtime) % 3600 / 60 as int) as varchar),2) + ':' + 
       right('00'+ cast(cast(datediff(ss ,@starttime,@endtime) % 60 as int) as varchar),2)


/*--------------
47:58:58*/

[bancxc]

[code=SQL]
declare @s int
set @s=230 --分钟
select ltrim(@s/60)+'小时'+ltrim(@s%60)+'分'

------------------------------
3小时50分
[/CODE]

[luoyoumou]

http://www.lan27.com/Article/200708/2002.htm

---楼主：去看一下这个，你就知道了

[sunboy_163]

查询停电时间：
select top 1 rd_time from reading where pk_id=2546 and rd_ac='断电' order by rd_id
查询结果：2009-10-10 12:15:16

查询停电终于时间：
select top 1 rd_time from reading where pk_id=2546 and rd_ac='断电' order by rd_id desc
查询结果：2009-10-10 12:22:28


如何写一条SQL语句？计算一共停了多长时间电？应该是：（2009-10-10 12:22:28-2009-10-10 12:15:16)=7分钟的样子？

格式：
小时:分钟

表示!

[luoyoumou]

--去除60....，都麻烦！
去看一下这个,会告诉你：什么是最简便的方法：
http://www.lan27.com/Article/200708/2002.htm

[sunboy_163]

[Quote=引用 24 楼 luoyoumou 的回复:]
--去除60....，都麻烦！
去看一下这个,会告诉你：什么是最简便的方法：
http://www.lan27.com/Article/200708/2002.htm
[/Quote]

多谢大家！不管了！分钟转换成小时分，我用程序转换吧！再次感谢大家！

[majemaje]

学习了。。。。