SQL中YYYYMMDD格式日期字符串转日期的错误排查

嘿，问题出在你对substring函数的参数理解上啦！大部分SQL数据库里，substring的第三个参数是截取的长度，而不是你以为的结束位置。

你写的substring([column],5,6)是从第5位开始，一口气截取6个字符，但你的日期字符串总共才8位（比如20180521），这就会导致截取到超出字符串长度的内容，甚至把后面的部分乱取，才出现了2018-0521 -21这种奇怪的结果。

正确的截取写法 #

对于20180211这种8位的日期字符串，我们需要按固定长度截取年、月、日：

• 年份：从第1位开始取4个字符 → substring([column],1,4)
• 月份：从第5位开始取2个字符 → substring([column],5,2)
• 日期：从第7位开始取2个字符 → substring([column],7,2)

修正后的SQL语句如下：

select column, substring([column],1,4) + '-' + substring([column],5,2) + '-' + substring([column],7,2) as newcol1 FROM table

进一步转换为日期类型 #

如果还要把格式化后的字符串转换成日期类型，可以用CAST或CONVERT函数：

select column, 
       CAST(substring([column],1,4) + '-' + substring([column],5,2) + '-' + substring([column],7,2) AS DATE) as newcol1 
FROM table

更简便的数据库特定方法 #

其实很多数据库都有直接处理这种格式的函数，不用手动截取：

• MySQL：用STR_TO_DATE直接解析格式

  select column, STR_TO_DATE([column], '%Y%m%d') as newcol1 FROM table
  

• SQL Server：用CONVERT指定格式代码（112对应YYYYMMDD的ISO格式）

  select column, CONVERT(DATE, [column], 112) as newcol1 FROM table
  

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