sql 求时间差

 

 

时分秒 格式（12:30:00）

CREATE FUNCTION [dbo].[F_TimeBetween]
(
@start datetime,
@end datetime,
@PlanQty int  -- 这个是用来算平均时间的
)
RETURNS varchar(10)
AS
BEGIN
  IF @PlanQty IS NULL
     RETURN right('00'+cast(cast(datediff(ss,@start,@end)/3600 as int) as varchar),2)+':'+
            right('00'+cast(cast(datediff(ss,@start,@end)%3600/60 as int) as varchar),2)+':'+
            right('00'+cast(cast(datediff(ss,@start,@end)%60 as int) as varchar),2)
  ELSE
     RETURN right('00'+cast(cast((datediff(ss,@start,@end)/@PlanQty)/3600 as int) as varchar),2)+':'+
            right('00'+cast(cast((datediff(ss,@start,@end)/@PlanQty)%3600/60 as int) as varchar),2)+':'+
            right('00'+cast(cast((datediff(ss,@start,@end)/@PlanQty)%60 as int) as varchar),2)
    RETURN ''
END

GO

 

秒转天

CREATE FUNCTION [dbo].[F_ConvertToDay]
(
@Time decimal(10)
)
RETURNS varchar(100)
AS
BEGIN
  IF @Time = 0
     RETURN '0 天'
  ELSE
     RETURN 
          CAST(CAST(@Time / (60*60*24) AS decimal(10,3)) AS VARCHAR) + ' 天'
    RETURN ''
END



GO

 

格式（**天 *小时*分*秒）

ALTER FUNCTION [dbo].[F_ConvertTime]
(
@Time decimal(10)
)
RETURNS varchar(100)
AS
BEGIN
  IF @Time = 0
     RETURN '00:00:00'
  ELSE
     RETURN 
          CAST(CAST(@Time / (60*60*24) AS INT) AS VARCHAR) + '天'
        + CAST(CAST(@Time % 86400 / 3600 AS INT) AS VARCHAR) + '小时'
        + CAST(CAST(@Time % 3600 / 60 AS INT) AS VARCHAR) + '分'
        + CAST(CAST(@Time % 60 AS INT) AS VARCHAR) + '秒'
    RETURN ''
END

GO