全部

急! 在线等待 !SQL语句如何获取时间差 !

AA 2006-11-24 03:37:14

如题!
select Datediff(y ,recep_time,Getdate()) from vars1 这样读出来的无非就是
单独的日期（如：天。月。小时阿）只能得到单独的时间
我想得到一个连贯的时间如（滞留时间 = 当前时间- 申请时间）
公文标题滞留时间提交人
出差申请 10天11小时23分钟 admin
出差申请 16天22小时35分钟 admin
出差申请 16天22小时51分钟 admin
我想出来这样的效果！！！

...全文

1041 点赞收藏 17

写回复

17 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

AA 2006-11-24

求救 ~~~~!!@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

回复赞

AA 2006-11-24

Select Distinct work_styleid,work_tablename,porxy_man,blac_id,proxy_endtime ,proxy_starttime ,push,task_name,proxy_id,curtaskid,task_index,table_id,role_id,isdeal,id,work_id,archives_id,work_name ,prears_man,curars_man,recep_time ,ars_owner From vars1 Where (((curars_man = 'admin' and (proxy_id='' or (proxy_starttime >=getdate() or proxy_endtime <=getdate())) and blac_id='C620346Z9VSY1SZ1CB7') or (proxy_id='KsUserC620346Z9VSY1SZ1CB7' and proxy_starttime <=getdate() and proxy_endtime >=getdate())) or (archives_id =(SELECT DISTINCT arsid FROM thead WHERE isdeal = 0 AND arsid = vars1.archives_id AND task_id = vars1.curtaskid AND role_key='KsUserC620346Z9VSY1SZ1CB7') and (blac_id='C620346Z9VSY1SZ1CB7' and (proxy_id='' or (proxy_starttime >=getdate() or proxy_endtime <=getdate())) or (proxy_id='KsUserC620346Z9VSY1SZ1CB7' and proxy_starttime <=getdate() and proxy_endtime >=getdate() )))) order by recep_time desc

这个我以前的 SQL语句。现在想把才大家们帮我搞得SQL语句连成一个大的SQL语句阿 union
这个语句来连哦。。。小弟语句不行2哦请大家们帮哈哦。。。

Select Distinct work_styleid,work_tablename,porxy_man,blac_id,proxy_endtime ,proxy_starttime ,push,task_name,proxy_id,curtaskid,task_index,table_id,role_id,isdeal,id,work_id,archives_id,work_name ,prears_man,curars_man,recep_time ,ars_owner From vars1 Where (((curars_man = 'admin' and (proxy_id='' or (proxy_starttime >=getdate() or proxy_endtime <=getdate())) and blac_id='C620346Z9VSY1SZ1CB7') or (proxy_id='KsUserC620346Z9VSY1SZ1CB7' and proxy_starttime <=getdate() and proxy_endtime >=getdate())) or (archives_id =(SELECT DISTINCT arsid FROM thead WHERE isdeal = 0 AND arsid = vars1.archives_id AND task_id = vars1.curtaskid AND role_key='KsUserC620346Z9VSY1SZ1CB7') and (blac_id='C620346Z9VSY1SZ1CB7' and (proxy_id='' or (proxy_starttime >=getdate() or proxy_endtime <=getdate())) or (proxy_id='KsUserC620346Z9VSY1SZ1CB7' and proxy_starttime <=getdate() and proxy_endtime >=getdate() )))) order by recep_time desc union (select cast(Datediff(day ,recep_time,Getdate()) as varchar) + '天' +
cast(Datediff(hour ,recep_time,Getdate()) % 24 as varchar) + '小时' +
cast(Datediff(minute ,recep_time,Getdate()) % 60 as varchar) + '分钟' as 滞留时间 from vars1)
好像有问题哦。。应该怎么连哦。。。 SQL高手们····快帮哈沃阿

回复赞

AA 2006-11-24

阿~！！急啊大哥们。。新的问题出来啦。。。

回复赞

dulei115 2006-11-24

直接 datediff(day 不对吧，当时间部分晚于现在时间时结果就错了，例如：

select cast(Datediff(day ,'2006-10-10 17:00',Getdate()) as varchar) + '天' +
cast(Datediff(hour ,'2006-10-10 17:00',Getdate()) % 24 as varchar) + '小时' +
cast(Datediff(minute ,'2006-10-10 17:00',Getdate()) %60 as varchar) + '分钟' as 滞留时间
--45天23小时5分钟

只能是用 datediff(minute 来 / 和 %

回复赞

AA 2006-11-24

现在时间是解决拉！！！感谢~！！！！

回复赞

dawugui 2006-11-24

对.楼上的,我多做了一步.

select cast(Datediff(day ,'2006-10-10',Getdate()) as varchar) + '天' +
cast(Datediff(hour ,'2006-10-10',Getdate()) % 24 as varchar) + '小时' +
cast(Datediff(minute ,'2006-10-10',Getdate()) %60 as varchar) + '分钟' as 滞留时间

滞留时间
-------------------
45天15小时55分钟

（所影响的行数为 1 行）

回复赞

playwarcraft 2006-11-24

小時直接對24取余
分鐘直接對60取余
應該這樣就OK了

回复赞

playwarcraft 2006-11-24

declare @t datetime
set @t='2006-10-10'

select convert(varchar(04),datediff(day,@t,getdate()))+'日'+convert(varchar(04),datediff(hour,@t,getdate())%24)+'小時'+convert(varchar(04),datediff(minute,@t,getdate())%60)+'分鐘'

----------------------
45日15小時50分鐘

回复赞

dulei115 2006-11-24

if object_id('MyDateDiff') is not null drop function MyDateDiff
go
create function MyDateDiff(@BeginDate datetime, @EndDate datetime)
returns varchar(20)
as
begin
declare @n int,@s varchar(20)
set @n = datediff(minute, @BeginDate, @EndDate)
set @s = cast(@n / 60 / 24 as varchar) + '天'
set @n = @n % (60 * 24)
set @s = @s + cast(@n / 60 as varchar) + '小时'
set @s = @s + cast(@n % 60 as varchar) + '分钟'
return @s
end
go
select dbo.MyDateDiff('2006-11-01 16:00:00', getdate())
--22天23小时50分钟
drop function MyDateDiff

回复赞

AA 2006-11-24

我来试试！！！！！！感谢大哥。。。马上给分

回复赞

dawugui 2006-11-24

这下可以了.

回复赞

dawugui 2006-11-24

select cast(Datediff(day ,'2006-10-10',Getdate()) as varchar) + '天' +
cast(Datediff(hour ,'2006-10-10',Getdate()) % 24 as varchar) + '小时' +
cast(Datediff(minute ,'2006-10-10',Getdate()) % 1440%60 as varchar) + '分钟' as 滞留时间

滞留时间
--------------------------
45天15小时47分钟

（所影响的行数为 1 行）

回复赞

dawugui 2006-11-24

分钟那里错了一点,我再看看.

回复赞

dawugui 2006-11-24

我我的'2006-10-10'替换为你的recep_time即可.

回复赞

playwarcraft 2006-11-24

換言之就是把" xxxx分鐘" 變成" x天x小時x分鐘" ??

回复赞

dawugui 2006-11-24

select cast(Datediff(day ,'2006-10-10',Getdate()) as varchar) + '天' as 天,
cast(Datediff(hour ,'2006-10-10',Getdate()) % 24 as varchar) + '小时' as 小时 ,
cast(Datediff(minute ,'2006-10-10',Getdate()) % 1440 as varchar) + '分钟' as 分钟

天小时分钟
---- ------ -------
45天 15小时 944分钟

（所影响的行数为 1 行）

select cast(Datediff(day ,'2006-10-10',Getdate()) as varchar) + '天' +
cast(Datediff(hour ,'2006-10-10',Getdate()) % 24 as varchar) + '小时' +
cast(Datediff(minute ,'2006-10-10',Getdate()) % 1440 as varchar) + '分钟' as 滞留时间

滞留时间
-------------------
45天15小时945分钟

（所影响的行数为 1 行）

回复赞

dulei115 2006-11-24

那自己写个函数转换

回复赞