SQL Server自定义函数varchar转hh:mm:ss失败问题排查

原函数代码 #

ALTER FUNCTION [dbo].[ConvertTimeToHH:MM:SS] 
(
    @P_Value NVARCHAR(50)
)
RETURNS NVARCHAR(50)
BEGIN
DECLARE @Result NVARCHAR(50)=''
DECLARE @Result1 NVARCHAR(50)=''
IF @P_Value !=''
BEGIN
    SET @Result = '(SELECT CONVERT(@P_Value,GETDATE(),108) as [hh:mm:ss])';
    RETURN @Result 
END
SET @Result1='enter some value'

RETURN @Result1

END 

问题现象 #

调用函数时：

• 输入 SELECT [dbo].[ConvertTimeToHH:MM:SS] ('14')，期望返回 14:00:00，实际返回字符串 (SELECT CONVERT(@P_Value,GETDATE(),108) as [hh:mm:ss])
• 输入 SELECT [dbo].[ConvertTimeToHH:MM:SS] ('14:3')，期望返回 14:03:00，实际同样返回上述字符串

问题分析 #

1. 仅返回字符串未执行SQL：你只是把SQL语句拼接成字符串赋值给@Result后直接返回，没有实际执行这条语句。SQL Server标量函数中这种写法只会返回字符串本身，不会解析执行其中的SQL逻辑。
2. CONVERT函数用法错误：CONVERT的标准语法是CONVERT(data_type, expression, style)，你把输入参数@P_Value放在了第一个参数位（应为目标数据类型），把GETDATE()作为第二个参数（应为要转换的表达式），参数顺序和用途完全不符合你的需求，而且你的目标是补全时间片段，不是转换当前日期的格式。
3. 函数命名不规范：函数名包含冒号，虽然SQL Server允许，但不符合常规命名规范，易引发混淆，建议改为无特殊符号的名称。

修正方案 #

不需要使用动态SQL，直接处理输入的时间片段字符串，补全缺失的分、秒部分，再转换为标准的HH:MM:SS格式。以下是修正后的函数：

ALTER FUNCTION [dbo].[ConvertTimeToHHMMSS] 
(
    @P_Value NVARCHAR(50)
)
RETURNS NVARCHAR(8)
BEGIN
    DECLARE @Result NVARCHAR(8) = ''
    
    IF @P_Value IS NULL OR LTRIM(RTRIM(@P_Value)) = ''
    BEGIN
        RETURN '请输入有效值'
    END

    -- 处理输入字符串，补全分、秒
    DECLARE @TimeStr NVARCHAR(20) = LTRIM(RTRIM(@P_Value))
    -- 统计冒号数量
    DECLARE @ColonCount INT = (LEN(@TimeStr) - LEN(REPLACE(@TimeStr, ':', '')))

    SET @TimeStr = CASE
        -- 只有小时，如'14'，补全分秒
        WHEN @ColonCount = 0 THEN @TimeStr + ':00:00'
        -- 有小时和分钟，如'14:3'，补全分钟为两位后补秒
        WHEN @ColonCount = 1 THEN 
            LEFT(@TimeStr, CHARINDEX(':', @TimeStr)) + 
            RIGHT('0' + SUBSTRING(@TimeStr, CHARINDEX(':', @TimeStr)+1, LEN(@TimeStr)), 2) + 
            ':00'
        -- 已有完整格式，直接使用
        ELSE @TimeStr
    END

    -- 转换为时间类型再格式化输出，确保格式正确
    IF ISDATE(@TimeStr) = 1
    BEGIN
        SET @Result = CONVERT(NVARCHAR(8), CAST(@TimeStr AS TIME), 108)
    END
    ELSE
    BEGIN
        SET @Result = '无效的时间格式'
    END

    RETURN @Result
END

测试验证 #

• 调用 SELECT [dbo].[ConvertTimeToHHMMSS] ('14')，返回 14:00:00
• 调用 SELECT [dbo].[ConvertTimeToHHMMSS] ('14:3')，返回 14:03:00
• 调用 SELECT [dbo].[ConvertTimeToHHMMSS] ('14:30:25')，返回 14:30:25
• 调用 SELECT [dbo].[ConvertTimeToHHMMSS] ('')，返回 请输入有效值

内容的提问来源于stack exchange，提问作者MR GREEN