需求描述

我要从一个固定格式的字符串中按要求提取时间，比如435741212410220002406560 是一个包含订单号和时间信息的字符串，格式 {订单8位数字}{yyMMddHHmmss}，用sql把时间提取出来，转成时间格式示例 2024-10-22 00:02:41.177。

看看各个大模型的处理结果，这次对比通义千问（通义千问2.5）、文心一言（文心大模型3.5）、ChatGPT（ChatGPT-4），都是免费版本。

通义千问

通义千问的回答有点不尽如人意。。。

给出的方案是使用了STUFF函数，看着非常的繁琐，可读性很差。

试试执行结果：

直接报错了：[S0003][242] Line 7: 从 varchar 数据类型到 datetime 数据类型的转换产生一个超出范围的值。

就是使用了STUFF函数这一行报的错误。

告诉它，让它修正。

它指出了原因，转换失败的原因可能是时间部分的格式不完全符合 DATETIME 类型的要求，或者时间值本身超出了 DATETIME 类型的有效范围（1753-01-01 到 9999-12-31）。此外，毫秒部分在最初的转换中没有被正确处理。

再给出了另一个解决方案：

DECLARE @OrderInfo VARCHAR(50) = '435741212410220002416560';

-- 提取订单号和时间部分
DECLARE @OrderNumber VARCHAR(8) = LEFT(@OrderInfo, 8);
DECLARE @TimePart VARCHAR(14) = SUBSTRING(@OrderInfo, 9, 14);

-- 将时间部分转换为日期时间格式
DECLARE @DateTimeWithMillis DATETIME2(3) = 
    CONVERT(DATETIME2(3), 
            STUFF(STUFF(STUFF(
                LEFT(@TimePart, 8) + ' ' + SUBSTRING(@TimePart, 9, 6) + '.' + RIGHT('000' + RIGHT(@TimePart, 3), 3),
                5, 0, '-'),
                8, 0, '-'),
                11, 0, ':'),
            14, 0, ':');

-- 输出结果
SELECT @DateTimeWithMillis AS ExtractedDateTimeWithMillis;

执行结果：

直接报错了。。。这又用了 STUFF 函数，语句也是很复杂，可读性一言难尽。

其实相比 DATETIME，DATETIME2 提供了更高的精度，支持从 0 到 7 的秒小数精度，默认精度为 7。是SQL Server 2008 的新增特性之一。

我告诉它提示错误 Unknown database function 'DATETIME2'。备注：我的数据库客户端 DataGrip 没有识别，确实有点诧异。

它又给了另一个解决方案：

DECLARE @OrderInfo VARCHAR(50) = '435741212410220002416560';

-- 提取时间部分
DECLARE @TimePart VARCHAR(14) = SUBSTRING(@OrderInfo, 9, 14);

-- 构建日期时间字符串
DECLARE @DateTimeString VARCHAR(23) = 
    LEFT(@TimePart, 4) + '-' + -- 年份
    SUBSTRING(@TimePart, 5, 2) + '-' + -- 月份
    SUBSTRING(@TimePart, 7, 2) + ' ' + -- 日
    SUBSTRING(@TimePart, 9, 2) + ':' + -- 小时
    SUBSTRING(@TimePart, 11, 2) + ':' + -- 分钟
    SUBSTRING(@TimePart, 13, 2) + '.' + -- 秒
    RIGHT('000' + SUBSTRING(@TimePart, 15, 3), 3); -- 毫秒

-- 将构建的字符串转换为 DATETIME 类型
DECLARE @DateTime DATETIME;
SET @DateTime = CONVERT(DATETIME, @DateTimeString, 121); -- 使用样式121 (ODBC Canonical)

-- 输出结果
SELECT @DateTime AS ExtractedDateTime;

执行还是报错：

还是从 varchar 数据类型到 datetime 数据类型的转换产生一个超出范围的值。

打印变量 @DateTimeString 值是 2410-22-00 02:41:65.000 发现压根没有考虑我最初给的格式 {订单8位数字}{yyMMddHHmmss}

自己改进下：

DECLARE @OrderInfo VARCHAR(50) = '435741212410220002416560';

-- 提取时间部分
DECLARE @TimePart VARCHAR(14) = SUBSTRING(@OrderInfo, 9, 12);

-- 构建日期时间字符串
DECLARE @DateTimeString VARCHAR(23) =
    '20' + LEFT(@TimePart, 2) + '-' + -- 年份
    SUBSTRING(@TimePart, 3, 2) + '-' + -- 月份
    SUBSTRING(@TimePart, 5, 2) + ' ' + -- 日
    SUBSTRING(@TimePart, 7, 2) + ':' + -- 小时
    SUBSTRING(@TimePart, 9, 2) + ':' + -- 分钟
    SUBSTRING(@TimePart, 11, 2) + '.' + -- 秒
    RIGHT('000' + SUBSTRING(@TimePart, 13, 3), 3); -- 毫秒

select @TimePart,@DateTimeString;

-- 将构建的字符串转换为 DATETIME 类型
DECLARE @DateTime DATETIME;
SET @DateTime = CONVERT(DATETIME, @DateTimeString, 121); -- 使用样式121 (ODBC Canonical)

-- 输出结果
SELECT @DateTime AS ExtractedDateTime;

则执行正常：

文心一言

DECLARE @inputString VARCHAR(24) = '435741212410220002406560';

-- 提取时间部分并转换为日期时间格式
DECLARE @timeString VARCHAR(12) = SUBSTRING(@inputString, 9, 12);
DECLARE @year CHAR(2) = SUBSTRING(@timeString, 1, 2);
DECLARE @month CHAR(2) = SUBSTRING(@timeString, 3, 2);
DECLARE @day CHAR(2) = SUBSTRING(@timeString, 5, 2);
DECLARE @hour CHAR(2) = SUBSTRING(@timeString, 7, 2);
DECLARE @minute CHAR(2) = SUBSTRING(@timeString, 9, 2);
DECLARE @second CHAR(2) = SUBSTRING(@timeString, 11, 2);

-- 将提取的各部分组合成完整的日期时间字符串
DECLARE @fullDateTimeString VARCHAR(23) = CAST(@year + '-' + @month + '-' + @day AS VARCHAR(10)) + ' ' +
                                         CAST(@hour + ':' + @minute + ':' + @second AS VARCHAR(8));

-- 将字符串转换为 DATETIME 类型，并格式化输出
DECLARE @dateTime DATETIME = CONVERT(DATETIME, @fullDateTimeString, 120);
DECLARE @formattedDateTime VARCHAR(23) = CONVERT(VARCHAR, @dateTime, 121); -- 121 格式为 yyyy-mm-dd hh:mi:ss.mmm

-- 最终结果
SELECT @formattedDateTime AS FormattedDateTime;

提取出来的时间格式字符串是 24-10-22 00:02:40 ，值是对的，但是转换失败了，当成了格式 yyyy-mm-dd，稍微改下就正常了：

ChatGPT

使用的 ChatGPT 模型版本：

给出结果如下：

SELECT 
    OrderString,
    CONVERT(DATETIME, 
        '20' + SUBSTRING(OrderString, 9, 2) + '-' +      -- 提取年份（加上 '20' ）
        SUBSTRING(OrderString, 11, 2) + '-' +            -- 提取月份
        SUBSTRING(OrderString, 13, 2) + ' ' +            -- 提取日期
        SUBSTRING(OrderString, 15, 2) + ':' +            -- 提取小时
        SUBSTRING(OrderString, 17, 2) + ':' +            -- 提取分钟
        SUBSTRING(OrderString, 19, 2)                    -- 提取秒
    ) AS 转换后的时间
FROM 
    OrderInfo;

找一个有这种格式字符串的表执行下：

执行结果也正确。

总结

三者对比，只以本次对比来说，ChatGPT-4表现最好，可以直接使用，文心大模型3.5稍微次一点，通义千问2.5给出的结果不光错误，可读性也较差。