如何将JSON中特定varchar格式日期转换为可读日期并解决转换报错

错误原因 #

你当前JSON中存储的actual_arrival、actual_departure字段为yyyyMMddHHmmss±时区偏移的自定义字符串格式，SQL Server的DATETIME类型不支持直接解析该格式，因此会触发Conversion failed when converting date and/or time from character string报错。

解决方案 #

先在OPENJSON的WITH子句中以字符串类型取出两个时间字段，再通过字符串处理+日期转换函数转为可读格式，可按需选择是否处理时区偏移：

方案1：保留时区偏移转换（推荐）

支持将原始时区的时间转为你需要的目标时区时间，示例中转为东八区北京时间：

Declare @json varchar(MAX) = '
{
    "stops": {
        "type": "stop",
        "name": "stops",
        "company_id": "xxx",
        "actual_arrival": "20210910130000-0500",
        "actual_departure": "20210910140000-0500"
    }
}';

SELECT 
    Type,
    Name,
    CompID,
    -- 可修改FORMAT第二个参数自定义输出格式，如改为'yyyy-MM-dd HH:mm:ss'输出纯数字格式
    FORMAT(SWITCHOFFSET(CAST(STUFF(STUFF(STUFF(STUFF(STUFF(AcArrvlStr, 13, 0, ':'), 11, 0, ':'), 9, 0, 'T'), 7, 0, '-'), 5, 0, '-') AS DATETIMEOFFSET), '+08:00'), 'yyyy年MM月dd日 HH:mm:ss') AS 实际到达时间,
    FORMAT(SWITCHOFFSET(CAST(STUFF(STUFF(STUFF(STUFF(STUFF(AcDprtrStr, 13, 0, ':'), 11, 0, ':'), 9, 0, 'T'), 7, 0, '-'), 5, 0, '-') AS DATETIMEOFFSET), '+08:00'), 'yyyy年MM月dd日 HH:mm:ss') AS 实际出发时间
FROM OPENJSON ( @json, '$.stops' )             
WITH (          
    Type Varchar(50) '$.type',
    Name Varchar(50) '$.name',
    CompID Varchar(100) '$.company_id' ,
    AcArrvlStr VARCHAR(30) '$.actual_arrival' ,
    AcDprtrStr VARCHAR(30) '$.actual_departure'
) as j1

方案2：忽略时区直接转换

如果不需要处理时区偏移，可以直接截取时间字符串前14位转换，写法更简洁：

SELECT 
    Type,
    Name,
    CompID,
    CONVERT(VARCHAR(20), CONVERT(DATETIME, LEFT(AcArrvlStr,14), 112), 120) AS 实际到达时间,
    CONVERT(VARCHAR(20), CONVERT(DATETIME, LEFT(AcDprtrStr,14), 112), 120) AS 实际出发时间
FROM OPENJSON ( @json, '$.stops' )             
WITH (          
    Type Varchar(50) '$.type',
    Name Varchar(50) '$.name',
    CompID Varchar(100) '$.company_id' ,
    AcArrvlStr VARCHAR(30) '$.actual_arrival' ,
    AcDprtrStr VARCHAR(30) '$.actual_departure'
) as j1

执行上述任意方案的查询后，结果就会以表格形式返回人类可读的日期格式。

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