T-SQL自定义函数ConvertSecondsToTime

来源：未知责任编辑：责任编辑发表时间:2013-12-18 11:37　点击:次

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

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: Insus.NET

-- Create date: 2012-03-24

-- Description: Convert second to time

-- =============================================

CREATE FUNCTION [dbo].[udf_ConvertSecondToTime]

(

@Seconds INTEGER

)

RETURNS TIME

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:

作者 Insus.NET