SQL Server如何将格式不统一的YYYYMMDD、YYYYMMDD-HHMM转换为mm/dd/yyyy hh:mm

问题原因 #

原有写法存在两处问题：

• 未区分带时间、不带时间的两种输入格式，无时间后缀的字符串执行RIGHT、SUBSTRING操作会返回无效值
• 时间部分提取逻辑错误：带-的YYYYMMDD-HHMM格式中，时间仅占后4位，原有RIGHT(ID , 6)会包含前缀的-字符，拆分时分时会出错

解决方案 #

用CASE WHEN判断输入是否包含-字符，分场景处理，无时间后缀的默认补00:00即可，以下是不同数据库环境的实现代码：

SQL Server 版本 #

SELECT
  CASE
    -- 匹配带时间的YYYYMMDD-HHMM格式
    WHEN CHARINDEX('-', id) > 0
    THEN FORMAT(
        CONVERT(DATETIME, LEFT(id, 8), 120) 
        + CAST(STUFF(RIGHT(id,4), 3, 0, ':') AS DATETIME),
        'MM/dd/yyyy HH:mm'
    )
    -- 匹配不带时间的YYYYMMDD格式，默认补00:00
    ELSE CONVERT(VARCHAR, CONVERT(DATETIME, LEFT(id, 8), 120), 101) + ' 00:00'
  END AS target_format_time
FROM 你的表名

MySQL 版本 #

SELECT
  CASE
    WHEN INSTR(id, '-') > 0
    THEN DATE_FORMAT(
        STR_TO_DATE(CONCAT(LEFT(id,8), ' ', INSERT(RIGHT(id,4),3,0,':')), '%Y%m%d %H:%i'),
        '%m/%d/%Y %H:%i'
    )
    ELSE DATE_FORMAT(STR_TO_DATE(LEFT(id,8), '%Y%m%d'), '%m/%d/%Y 00:00')
  END AS target_format_time
FROM 你的表名

内容的提问来源于stack exchange，提问作者Jian Gui Li