create function GetTimeDiff( @beginDateTime datetime, @endDateTime datetime)
returns nvarchar(100)
as
begin
declare @totalMS int ,@totalS int , @totalH int , @totalDay int ,@return nvarchar(100)
set @totalMS = datediff(ss,@beginDateTime, @endDateTime)
set @totalS = @totalMS/60
set @totalMS = @totalMS % 60
set @totalH = @totalS/60
set @totalS = @totalS%60
set @totalDay= @totalH/24
set @totalH = @totalH % 24
set @return = ''
if @totalDay<>0 --天
set @return = @return+cast(@totalDay as varchar(10))+'天'
if @totalH<>0 -- 小时
set @return = @return+cast(@totalH as varchar(2))+'小时'
if @totalS<>0 -- 分钟
set @return = @return+cast(@totalS as varchar(2))+'分'
if @totalMS<>0 --秒
set @return = @return+cast(@totalMS as varchar(2))+'秒'
也可以改写为一个函数
Create Function GetTimeDiff(@STime DateTime,@ETime DateTime)
Returns Nvarchar(100)
As
Begin
Declare @S Nvarchar(100)
Set @S=Rtrim(DateDiff(dd,@STime,@ETime))+N'天'
+Rtrim(DateDiff(hh,@STime,@ETime)-DateDiff(dd,@STime,@ETime)*24)+N'小时'
+Rtrim(DateDiff(mi,@STime,@ETime)-DateDiff(hh,@STime,@ETime)*60)+N'分钟'
Return @S
End
GO
Select dbo.GetTimeDiff('2005-7-8 11:47:03','2005-9-8 12:50:03')