T-SQL自定义函数ConvertSecondsToTime

MS SQL Server一个自定义函数[dbo].[udf_ConvertSecondToTime]，把秒数转换为时间。

传入的值最大为86399，如果大于这个数值，这将会出现异常：

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value. 

 
   
   SET ANSI_NULLS  
   ON 
   
GO 
   
SET QUOTED_IDENTIFIER  
   ON 
   
GO 
   
-- 
    ============================================= 
   
-- 
    Author:        Insus.NET 
   
-- 
    Create date:   2012-03-24 
   
-- 
    Description:   Convert second to time 
   
-- 
    ============================================= 
   
 
   CREATE  
   FUNCTION  
   [ 
   dbo 
   ]. 
   [udf_ 
   ConvertSecondToTime 
   ]  
   
( 
   
     
   @Seconds  
   INTEGER  
   
) 
   
RETURNS TIME 
   
AS 
   
BEGIN 
   
DECLARE   
   
     
   @H  
   AS  
   NVARCHAR( 
   2)  
   =  
   CAST( 
   ROUND( 
   @Seconds  
   /  
   3600,  
   0)  
   AS  
   NVARCHAR), 
   
     
   @M  
   AS  
   NVARCHAR( 
   2)  
   =  
   CAST( 
   ROUND((( 
   @Seconds  
   %  
   3600)  
   /  
   60), 
   0)  
   AS  
   NVARCHAR), 
   
     
   @S  
   AS  
   NVARCHAR( 
   2)  
   =   
   CAST( 
   ROUND((( 
   @Seconds  
   %  
   3600)  
   %  
   60), 
   0)  
   AS  
   NVARCHAR) 
   
RETURN  
   CAST(( 
   @H  
   +  
   ' 
   : 
   '  
   +  
   @M  
   +  
   ' 
   : 
   '  
   +  
   @S)  
   AS TIME)  
   
END 
   
 

 

Example:

 
  SELECT  
  [ 
  dbo 
  ]. 
  [ 
  udf_ConvertSecondsToTime 
  ]( 
  477) 
  
SELECT  
  [ 
  dbo 
  ]. 
  [ 
  udf_ConvertSecondsToTime 
  ]( 
  86399) 
 

 

Result:

 

See also:

http://www.cnblogs.com/insus/articles/1937683.html