如何创建TSQL函数移除下划线前缀并格式化字符串中的日期时间

一、单行查询快速实现 #

直接通过字符串函数组合，一步完成下划线前缀移除、日期时间格式化：

Declare @inputstring as varchar(50) = 'Studio9_20230126_203052';

SELECT 
    CONVERT(DATETIME, 
        -- 处理日期部分：20230126 → 2023-01-26
        STUFF(STUFF(SUBSTRING(@inputstring, CHARINDEX('_', @inputstring)+1, 8), 5, 0, '-'), 8, 0, '-')
        + ' ' +
        -- 处理时间部分：203052 → 20:30:52
        STUFF(STUFF(SUBSTRING(@inputstring, CHARINDEX('_', @inputstring)+10, 6), 3, 0, ':'), 6, 0, ':')
    ) AS FormattedDateTime;

逻辑说明：

• CHARINDEX('_', @inputstring)定位第一个下划线的位置，通过SUBSTRING分别提取日期（下划线后8位）和时间（下划线后第10位开始的6位）
• STUFF函数在指定位置插入分隔符，将纯数字格式的日期时间转为带分隔符的标准格式
• CONVERT(DATETIME, ...)将拼接后的字符串转为日期时间类型，自动补充.000毫秒部分

二、封装为可复用的TSQL函数 #

如果需要多次使用该逻辑，可以创建标量函数，同时增加格式校验避免异常：

CREATE FUNCTION dbo.ExtractAndFormatDateTime(@inputstring VARCHAR(50))
RETURNS DATETIME
AS
BEGIN
    -- 空输入或无下划线的情况直接返回NULL
    IF @inputstring IS NULL OR CHARINDEX('_', @inputstring) = 0
        RETURN NULL;

    -- 提取下划线后的完整日期时间片段（如20230126_203052）
    DECLARE @dtFragment VARCHAR(20) = SUBSTRING(@inputstring, CHARINDEX('_', @inputstring)+1, LEN(@inputstring));

    -- 校验片段格式是否符合「8位日期_6位时间」的规则
    IF LEN(@dtFragment) != 15 OR CHARINDEX('_', @dtFragment) != 9
        RETURN NULL;

    -- 格式化并返回结果
    RETURN CONVERT(DATETIME, 
        STUFF(STUFF(LEFT(@dtFragment, 8), 5, 0, '-'), 8, 0, '-')
        + ' ' +
        STUFF(STUFF(RIGHT(@dtFragment, 6), 3, 0, ':'), 6, 0, ':')
    );
END;

函数使用示例：

-- 正常输入
SELECT dbo.ExtractAndFormatDateTime('Studio9_20230126_203052') AS FormattedDateTime;

-- 异常输入（返回NULL）
SELECT dbo.ExtractAndFormatDateTime('InvalidString') AS FormattedDateTime;

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