求一个sql函数

Question

求一个sql函数如题：两个时间相减得到几天几分几秒如:startDate='2011-06-25 19:11:32.877',endDate='2011-08-01 08:18:20.126'求各位高手解答

Answer 1

declare @i int
set @i = datediff(ss,'2011-06-25 19:11:32.877','2011-08-01 08:18:20.126')select ltrim(@i/3600)+':'+ltrim(@i%3600/60)+':'+ltrim(@i%3600%60)
???

Answer 2

declare @i int
set @i = datediff(ss,'2011-06-25 19:11:32.877','2011-08-01 08:18:20.126')select right(@i/(3600*24)+100,2)+' '+right(@i%(3600*24)/3600+100,2)+':'
+right(@i%(3600*24)/3600%3600/60+100,2)+':'+right(@i%(3600*24)/3600%3600%60+100,2)/********-------------------
36 13:00:13

Answer 3

declare @i int
set @i = datediff(ss,'2011-06-25 19:11:32.877','2011-08-01 08:18:20.126')select right(@i/(3600*24)+100,2)+' '+right(@i%(3600*24)/3600+100,2)+':'
+right(@i%(3600*24)%3600/60+100,2)+':'+right(@i%(3600*24)%3600%60+100,2)
3楼的错了。this.

Answer 4

select *,
convert(nvarchar(50),(DATEDIFF(SS,'2009-02-26 17:56:49.273','2009-02-28 16:57:58.177')/3600))+'小时'+
convert(nvarchar(50),(((DATEDIFF(SS,'2009-02-26 17:56:49.273','2009-02-28 16:57:58.177') )%3600)/60))+'分'+
convert(nvarchar(50),(((DATEDIFF(SS,'2009-02-26 17:56:49.273','2009-02-28 16:57:58.177') )%3600 )%60))+'秒'
from table

Answer 5

create proc shijian
   @starttime   datetime,
   @endtime     datetime
as
begin
declare
    @i    int
 
set @i=DATEDIFF(ss,@starttime,@endtime)
select cast((@i/(3600*24)) as varchar)+' '+right(@i%(3600*24)/3600+100,2)+':'+RIGHT(@i%       (3600*24)%3600/60+100,2)
+':'+RIGHT(@i%(3600*24)%3600%60+100,2)
end

Answer 6

CREATE FUNCTION fc_datediff(@startDate DATETIME,@endDate DATETIME)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @dd INT 
DECLARE @hh INT
DECLARE @mm INT 
DECLARE @ss INT 
DECLARE @diff VARCHAR(100)
SET @dd=DATEDIFF(dd,@startDate,@endDate)
SET @hh=DATEDIFF(hh,@startDate,@endDate)%24
SET @mm=DATEDIFF(minute,@startDate,@endDate)%60
SET @ss=DATEDIFF(second,@startDate,@endDate)%(3600)%60
SET @diff=CAST(@dd AS VARCHAR(10))+'days  '+CAST(@hh AS VARCHAR(10))+':'+CAST(@mm AS VARCHAR(10))+':'+CAST(@ss AS VARCHAR(10))
RETURN @diff
ENDPRINT dbo.fc_datediff('2011-06-25 19:11:32.877','2011-08-01 08:18:20.126')

Answer 7

37days  13:7:48和小三的差了1分钟！！！

Answer 8

DECLARE @STARTDATE DATETIME='2011-06-25 19:11:32.877'
DECLARE @ENDDATE DATETIME='2011-08-01 08:18:20.126'
SELECT ltrim(DATEDIFF(ss,@STARTDATE,@ENDDATE)/(3600*24))+':'+
convert(varchar(10),DATEADD(SS,DATEDIFF(ss,@STARTDATE,@ENDDATE)%(3600*24),'1970-01-01 00:00:00'),114) as '时间差'
-------------------------
时间差
-----------------------
36:13:06:48:0(1 行受影响)