函数：日期相减转换成 天 时 分 秒

Question

请问告诉，我写了一个函数，是用来把两个日期相减的秒数转换成天时分秒的格式，问题是:如果用sql语句转换是正常的，调用sql函数转换成的秒总是为00，数据库是2005， 请高手指点，附上代码
CREATE function [dbo].[fGetDHMS](@sTime varchar(50),@eTime varchar(50))
returns varchar(50)
as
begin
declare @str varchar(50)
if(datediff(ss,@sTime,@eTime)<0)
begin
set @str='-1'
end
else
begin
set @str=(select right('00'+ cast(cast(datediff(ss ,@sTime,@eTime) / 86400 as int) as varchar),2) + '天' + 
  right('00'+ cast(cast(datediff(ss ,@sTime,@eTime) % 86400 / 3600 as int) as varchar),2) + ':' + 
      right('00'+ cast(cast(datediff(ss ,@sTime,@eTime) % 86400 % 3600 / 60 as int) as varchar),2) + ':' + 
      right('00'+ cast(cast(datediff(ss ,@sTime,@eTime) % 86400 % 3600 % 60 as int) as varchar),2))
end
return @str
end下面是调试代码
declare @starttime as datetime
declare @endtime as datetime
set @starttime = '2013-08-18 10:09:21' 
set @endtime = '2013-08-19 18:19:50'select right('00'+ cast(cast(datediff(ss ,@starttime,@endtime) / 86400 as int) as varchar),2) + '天' + 
  right('00'+ cast(cast(datediff(ss ,@starttime,@endtime) % 86400 / 3600 as int) as varchar),2) + ':' + 
      right('00'+ cast(cast(datediff(ss ,@starttime,@endtime) % 86400 % 3600 / 60 as int) as varchar),2) + ':' + 
      right('00'+ cast(cast(datediff(ss ,@starttime,@endtime) % 86400 % 3600 % 60 as int) as varchar),2)
/*输出为：01天08:10:29*/
select dbo.fGetDHMS(@starttime,@endtime)
/*输出为：01天08:10:00*/

Answer 1

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

Answer 2

--函数本身没有错，错在两个时间参数的类型定义。--#1.调用函数传参时,两个datetime类型的变量,首先会自动转换为varchar(50)类型,此时就产生的误差:
declare @starttime as datetime
declare @endtime as datetime
set @starttime = '2013-08-18 10:09:21' 
set @endtime = '2013-08-19 18:19:50'
SELECT starttime=CAST(@starttime AS VARCHAR(50)), endtime=CAST(@endtime AS VARCHAR(50))
/*
starttime endtime
08 18 2013 10:09AM 08 19 2013  6:19PM
*/
--#2.由于#1产生了误差,函数再计算时产生的值就不对
DECLARE @sTime VARCHAR(50),@eTime VARCHAR(50)
SELECT @sTime = '08 18 2013 10:09AM', @eTime = '08 19 2013  6:19PM'
SELECT datediff(ss ,@sTime,@eTime) --错误值:115800
SELECT datediff(ss ,'2013-08-18 10:09:21','2013-08-19 18:19:50') --正确值:115829--结论:函数或存储过程的参数一定要定义成准确值,避免默认转换带来的一些不确定因素

Answer 3

--你把函数参数定义成datetime类型就OK了
alter function [dbo].[fGetDHMS](@sTime DATETIME,@eTime DATETIME)
--参考:
ALTER function [dbo].[fGetDHMS](@sTime DATETIME,@eTime DATETIME)
returns varchar(50)
as
begin
DECLARE @value int, @str varchar(50)
SET @value = datediff(ss ,@sTime,@eTime) if(@value < 0)
begin
set @str='-1'
end
else
begin
set @str=(select right('00'+ LTRIM(@value / 86400), 2) + '天' + 
  right('00'+ LTRIM(@value  % 86400 / 3600), 2) + ':' + 
  right('00'+ LTRIM(@value  % 86400 % 3600 / 60), 2) + ':' + 
  right('00'+ LTRIM(@value  % 86400 % 3600 % 60), 2))
end return @str
end--下面是调试代码
declare @starttime as datetime
declare @endtime as datetime
set @starttime = '2013-08-18 10:09:21' 
set @endtime = '2013-08-19 18:19:50'select dbo.fGetDHMS(@starttime,@endtime)
/*
(无列名)
01天08:10:29
*/