SQL Server中如何将yyyy-mm-ddThh:mm格式varchar转换为datetime2

问题说明 #

• 完整ISO8601格式（yyyy-mm-ddThh:mm:ssZ）可直接转换，代码如下：

SELECT CONVERT(datetime2, '2021-08-15T14:00:06Z')

转换结果：2021-08-15 14:00:06.0000000

• 截断到分钟的ISO8601格式（yyyy-mm-ddThh:mm）直接转换会报错：

SELECT CONVERT(datetime2, '2021-08-15T14:03')

报错内容：

从字符串转换日期和/或时间时，转换失败。

正确转换方法 #

方法1：补全秒级后缀后转换（适用于确认输入均为yyyy-mm-ddThh:mm格式的场景） #

直接在字符串末尾拼接:00补全秒部分，再执行转换即可，若需要固定输出yyyy-mm-dd hh:mm:ss.000格式，指定转换样式121即可：

-- 基础转换为datetime2类型
SELECT CONVERT(datetime2, '2021-08-15T14:03' + ':00')
-- 固定输出毫秒级字符串格式
SELECT CONVERT(VARCHAR, CONVERT(datetime2, '2021-08-15T14:03' + ':00'), 121)

执行结果：2021-08-15 14:03:00.000

方法2：兼容多格式的通用转换（同时支持带Z的完整格式、缺秒的短格式） #

通过判断字符串长度和处理末尾的Z字符，兼容多种输入格式，同时用TRY_CONVERT避免异常数据导致整条查询失败：

SELECT 
  CONVERT(
    datetime2,
    CASE 
      WHEN RIGHT(待转换字段名, 1) = 'Z' THEN LEFT(待转换字段名, LEN(待转换字段名)-1)
      ELSE 待转换字段名
    END + CASE WHEN LEN(待转换字段名) <= 16 THEN ':00' ELSE '' END
  ) AS 转换后时间
FROM 表名

如果需要固定输出毫秒级字符串格式，在外层套一层CONVERT(VARCHAR, 转换逻辑, 121)即可。

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