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.
udf_ConvertSecondToTime
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: