求剩余时间，精确到x年x月x日

[chenyuanzhong]

求剩余时间譬如2005-7-8 11:47:03到2005-9-8 12:50:03的剩余时间。结果格式为：10天3小时5分

[paoluo]

Select Rtrim(DateDiff(dd,'2005-7-8 11:47:03','2005-9-8 12:50:03'))+N'天'
+Rtrim(DateDiff(hh,'2005-7-8 11:47:03','2005-9-8 12:50:03')-DateDiff(dd,'2005-7-8 11:47:03','2005-9-8 12:50:03')*24)+N'小时'
+Rtrim(DateDiff(mi,'2005-7-8 11:47:03','2005-9-8 12:50:03')-DateDiff(hh,'2005-7-8 11:47:03','2005-9-8 12:50:03')*60)+N'分钟'
--结果
62天1小时3分钟

[paoluo]

Declare @STime DateTime,@ETime DateTime
Set @STime='2005-7-8 11:47:03'
Set @ETime='2005-9-8 12:50:03'
Select 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'分钟'

[paoluo]

也可以改写为一个函数
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')

[chenyuanzhong]

哥哥。谢了。我试试看。还以为没人理我呢

[chenyuanzhong]

这是在SQL SERVER中。我如果在asp中这样的一条sql语句怎么也。分可以 再加哈

[quhaihua]

这样写似乎是有问题的
比如Select dbo.GetTimeDiff('2005-7-8 11:47:03','2005-7-7 9:50:03')
-------------------------------------
-1天-2小时3分钟

[wgsasd311]

这是在SQL SERVER中。我如果在asp中这样的一条sql语句怎么也。分可以 再加哈
-------
在SQL中
Select dbo.GetTimeDiff('2005-7-8 11:47:03','2005-9-8 12:50:03')
在ASP中
sqlstr="Select dbo.GetTimeDiff('2005-7-8 11:47:03','2005-9-8 12:50:03')"
然后执行上面语句。

[pensheng]

Select rtrim(convert(char,datediff(day,'2005-7-8 11:47:03','2005-9-8 12:50:03')))
+'天'+rtrim(convert(char,datediff(hour,'2005-7-8 11:47:03','2005-9-8 12:50:03')-
datediff(day,'2005-7-8 11:47:03','2005-9-8 12:50:03')*24))
+'时'+rtrim(convert(char,datediff(minute,'2005-7-8 11:47:03','2005-9-8 12:50:03')-
datediff(hour,'2005-7-8 11:47:03','2005-9-8 12:50:03')*60))+'分'

[iwl]

up

[hzgood]

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))+'秒'

return replace(@return,' ','')
end