如何高效将ISO8601 Duration转换为datetime或time类型值？

我有一些ISO8601时长（注意不是ISO8601日期时间）数据，部分有效值示例如下：

P1D
PT0H
PT11M
P1DT2H15M
PT10H11M
PT2H46M12S

我希望将这些值解析为SQL Server的time或datetime2类型以便后续处理。目前我用字符串函数暴力解析，但代码复杂且容易出错，想请教有没有更优的解决方案。

当前使用的解析代码如下：

with anchors as
(
SELECT
 dt[duration]
, NULLIF(CHARINDEX('D', dt.duration), 0) As DLocation
, NULLIF(CHARINDEX('T', dt.duration), 0) As TLocation
, NULLIF(CHARINDEX('H', dt.duration), 0) As HLocation
, NULLIF(CHARINDEX('M', dt.duration), 0) As MLocation
, NULLIF(CHARINDEX('S', dt.duration), 0) As SLocation
, LEN(dt.duration)             as TotalLength
FROM dbo.DurationTest dt
)
SELECT
     duration
    ,DaysValue = CAST(ISNULL(SUBSTRING(duration, 2, (DLocation - 2)), 0) as tinyint)
    ,HoursValue = CAST(ISNULL(SUBSTRING(duration, TLocation + 1, (HLocation - TLocation) - 1 ), 0) as tinyint)
    ,MinutesValue = CAST(ISNULL(SUBSTRING(duration, COALESCE(HLocation, TLocation) + 1, MLocation - COALESCE(HLocation, TLocation) - 1), 0) as tinyint)
    ,SecondsValue = CAST(ISNULL(SUBSTRING(duration, COALESCE(MLocation, TLocation) + 1, SLocation - COALESCE(MLocation, TLocation) - 1 ), 0) as tinyint)
FROM anchors

这段代码能提取出天数、小时数、分钟数和秒数，后续转成总秒数或datetime2类型的方法我已经有成熟方案，但time类型仅支持小于24小时的值，所以我已经放弃使用该类型。

补充背景：这些数据来自ADP薪资Web服务，该字段用于记录每日工时，理论上应小于24小时，但我的数据集中存在部分异常值。

方案1：CLR集成解析（推荐） #

.NET的TimeSpan类型原生支持ISO8601时长解析，通过创建CLR函数可以直接复用这一成熟逻辑，避免手动字符串处理的错误：

步骤1：编写C#类库函数 #

using System;
using Microsoft.SqlServer.Server;

public class DurationParser
{
    [SqlFunction(DataAccess = DataAccessKind.None)]
    public static DateTime? ParseToDatetime2(string duration)
    {
        if (string.IsNullOrWhiteSpace(duration))
            return null;
            
        return TimeSpan.TryParse(duration, out var ts) 
            ? new DateTime(1, 1, 1) + ts 
            : null;
    }

    [SqlFunction(DataAccess = DataAccessKind.None)]
    public static long? ParseToTotalSeconds(string duration)
    {
        if (string.IsNullOrWhiteSpace(duration))
            return null;
            
        return TimeSpan.TryParse(duration, out var ts) 
            ? (long)ts.TotalSeconds 
            : null;
    }
}

步骤2：部署到SQL Server #

-- 开启CLR集成（需管理员权限）
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

-- 创建程序集
CREATE ASSEMBLY DurationParserAssembly
FROM 'C:\Your\Path\To\DurationParser.dll'
WITH PERMISSION_SET = SAFE;

-- 创建SQL函数
CREATE FUNCTION dbo.ParseIsoDurationToDatetime2(@duration NVARCHAR(100))
RETURNS DATETIME2
AS EXTERNAL NAME DurationParserAssembly.DurationParser.ParseToDatetime2;

CREATE FUNCTION dbo.ParseIsoDurationToSeconds(@duration NVARCHAR(100))
RETURNS BIGINT
AS EXTERNAL NAME DurationParserAssembly.DurationParser.ParseToTotalSeconds;

步骤3：使用示例 #

SELECT 
    duration,
    dbo.ParseIsoDurationToDatetime2(duration) AS DurationDatetime2,
    dbo.ParseIsoDurationToSeconds(duration) AS TotalSeconds
FROM dbo.DurationTest;

这个方案的优势是准确性高、容错性强，完全复用.NET的原生解析逻辑，无需维护复杂的字符串拆分代码。

方案2：纯SQL实现（无需CLR） #

如果无法启用CLR，可以通过字符串转换生成JSON，再用OPENJSON提取各时长分量计算：

SELECT
    dt.duration,
    -- 转换为datetime2（基准日期0001-01-01）
    DATEADD(SECOND, 
        ISNULL(JSON_VALUE(jsonData, '$.S'), 0) + 
        ISNULL(JSON_VALUE(jsonData, '$.M'), 0)*60 + 
        ISNULL(JSON_VALUE(jsonData, '$.H'), 0)*3600 + 
        ISNULL(JSON_VALUE(jsonData, '$.D'), 0)*86400,
        '0001-01-01') AS DurationDatetime2,
    -- 计算总秒数
    ISNULL(JSON_VALUE(jsonData, '$.S'), 0) + 
    ISNULL(JSON_VALUE(jsonData, '$.M'), 0)*60 + 
    ISNULL(JSON_VALUE(jsonData, '$.H'), 0)*3600 + 
    ISNULL(JSON_VALUE(jsonData, '$.D'), 0)*86400 AS TotalSeconds
FROM dbo.DurationTest dt
CROSS APPLY (
    -- 将ISO时长字符串转换为JSON格式
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        dt.duration,
        'P', '{"'),
        'D', '":'),
        'T', ',"'),
        'H', '":'),
        'M', '":'),
        'S', '":0}'
    ) AS jsonRaw
) jr
CROSS APPLY (
    -- 补全JSON闭合括号（处理不带秒的情况）
    SELECT JSON_QUERY(
        CASE WHEN CHARINDEX('S', jr.jsonRaw) = 0 THEN jr.jsonRaw + '}' ELSE jr.jsonRaw END
    ) AS jsonData
) jd;

该方案通过结构化的JSON解析替代原生字符串拆分，代码可读性和维护性比原方案更好。

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