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

 

posted @ 2022-01-12 10:04  林小强  阅读(837)  评论(0编辑  收藏  举报