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.
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
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)
SELECT [dbo].[udf_ConvertSecondsToTime](86399)
Result:
See also:
http://www.cnblogs.com/insus/articles/1937683.html
