【maxcompute|ODPS|SQL|HSQL】日期数据非标准日期格式（yyyy/M/d），如何转为yyyy-MM-dd HH:mm:ss标准格式
SQL中的日期数据转换真是一个复杂多变的问题啊！
一、需求及问题描述
在日常开发中，我们会遇到时间戳与标准日期格式之间的转换，两个日期差，及日期加加减减，想必大家都已经非常熟悉了
题主最近接到一个需求：
业务方给我一个表格，其中有一个截止日期字段
要求：
给定截止日期，查询截止日期前六个月的信息
但是Excel表格里面的日期是这样子的
把这些数据导入到ODPS中（题主是用阿里云的ODPS开发的)，导入数据建表是，该截止日期字段是STRING类型的
SELECT deadline FROM case_3;
由下图可见，导入之后日期格式仍然不是标准日期格式
如果我想查询截止日期在2024/9/1号之前6个月内的数据应该怎样限制日期呢？
应当是
time <= deadline and time >= deadline-6个月
我们先直接查一下，结果为true，说明time <= deadline这端是没问题的，阿里云的Maxcompute会自动转换类型进行加减，这是阿里云底层的优化
SELECT '2024-08-28 18:39:34'<'2024/9/1';
但是，我们再查下面的SQL，得到如下结果。显然，这并不符合我们本意。使用了ADD_MONTHS函数之后我们期望得到2024/6/1，这样才能与'2024-08-28 18:39:34'作对比。这篇文章主要介绍如何解决这个问题。
SELECT ADD_MONTHS('2024/9/1',-6),'2024-08-28 18:39:34'>ADD_MONTHS('2024/9/1',-6);
二、ADD_MONTH函数的用法
我在这里总结一下它的用法：
add_months函数需要接收两个参数
1、第一个参数是开始日期，它的数据类型可以是date|datetime|timestamp|string这些类型中的一个，date，datetime和string类型有对应的格式要求
2、第二个参数是一个整型的数字，用来表示加多少个月。返回的数据类型是string类型的，格式为yyyy-mm-dd。
详情见下面的介绍
命令格式
string add_months(date|datetime|timestamp|string <startdate>, int <num_months>)
参数说明
startdate：必填。DATE、DATETIME、TIMESTAMP或STRING类型，格式为yyyy-mm-dd、yyyy-mm-dd
hh:mi:ss或yyyy-mm-dd
hh:mi:ss.ff3。取值为STRING类型格式时，至少要包含yyyy-mm-dd且不含多余的字符串。num_months：必填。INT型数值。
返回值说明
返回开始日期startdate增加num_months个月后的日期，返回STRING类型的日期值，格式为yyyy-mm-dd。返回规则如下：
startdate非DATE、DATETIME、TIMESTAMP或STRING类型，或格式不符合要求时，返回NULL。
startdate值为NULL时，返回报错。
num_months值为NULL时，返回NULL。
三、格式化2024/9/1到2024-09-01
知道add_months函数的用法之后，我们现在面临的问题是，如何把不规范的日期规范。
注意：此时DATE_FORMAT函数已经不再适用，因为DATE_FORMAT的入参不支持接受2024/9/1格式的数据
string date_format(date|timestamp|string <date>, string <format>)
参数说明
date：必填。待转换的日期值。支持DATE、TIMESTAMP或STRING类型。
DATE和STRING类型只能在Hive兼容模式下使用，您可以执行set
odps.sql.hive.compatible=true;命令打开Hive模式。如果参数为
STRING类型，则格式只支持以下三种日期字段：‘yyyy-MM-dd’，例如’2019-12-27’。
‘yyyy-MM-dd hh:mm:ss’，例如’2019-12-27 12:23:10’。
‘yyyy-MM-dd hh:mm:ss.SSS’，例如’2019-12-27 12:23:10.123’。
format：必填。STRING类型常量。format可由如下日期字段组成，例如yyyy-MM-dd
hh:mm:ss.SSS或yyyy-MM-dd hh:mi:ss.SSS：
解决方案
补齐日期中缺失的位数，并把它转化为标准日期格式。
废话不多说，直接贴出来SQL，标准化之后的数据如下图所示，符合我们的预期：
SELECT deadline
,CONCAT(
-- 年份
SPLIT(deadline,'/')[0],'-',
-- 月份，确保两位
LPAD(SPLIT(deadline,'/')[1],2,'0'),'-',
-- 日，确保两位
LPAD(SPLIT(deadline,'/')[2],2,'0'),' 00:00:00') AS formatted_date
FROM case_3
;
这里着重介绍一下LPAD函数：
当需要格式化数据表中的字符串，以保证字符串输出的一致性和对齐时，使用LPAD函数，用字符串str2将字符串str1向左补足到length位。
总结一下它的用法：
LPAD函数接收三个参数
第一个参数为string类型，为需要补齐的字符串
第二个参数为Int类型，表示该字符串补齐后需要达到的长度或者位数
第三个参数为string类型，为长度不足时，使用该字符串补齐
返回类型为string类型
详情见下面的介绍
命令格式
string lpad(string <str1>, int <length>, string <str2>)
参数说明
str1：必填。STRING类型。待向左补位的字符串。
length：必填。INT类型。补位后的目标位数。
str2：必填。用于补位的字符串。
返回值说明
返回STRING类型。返回规则如下：
如果length小于str1的位数，则返回str1从左开始截取length位的字符串。
如果length为0，则返回空串。
如果没有输入参数或任一输入参数值为NULL，返回NULL。
如果length大于str1的位数且小于str1和str2的位数之和，则按照str2字符从左到右的顺序将str1字符串向左补足到length位。
我们来解释一下这段SQL
-- SPLIT(deadline,'/')[0] 使用'/切分字符串'，使其转化为一个数组，取数组第0位数据，即年(yyyy)所在的位置，年份不需要补齐
-- LPAD(SPLIT(deadline,'/')[1],2,'0') 使用'/切分字符串'，使其转化为一个数组，取数组第1位数据，即月(MM)所在的位置，月份需要向左补齐为两位，缺失的位数用0补齐
-- LPAD(SPLIT(deadline,'/')[2],2,'0') 使用'/切分字符串'，使其转化为一个数组，取数组第2位数据，即日(dd)所在的位置，日需要向左补齐为两位，缺失的位数用0补齐
-- 使用concat函数将年月日用'-'拼接，并给它拼接上时分秒
SELECT deadline
,CONCAT(
-- 年份
SPLIT(deadline,'/')[0],'-',
-- 月份，确保两位
LPAD(SPLIT(deadline,'/')[1],2,'0'),'-',
-- 日，确保两位
LPAD(SPLIT(deadline,'/')[2],2,'0'),' 00:00:00') AS formatted_date
FROM case_3
;
想必到这里各位读者已经很清楚了。
LPAD函数不仅在ODPS中可以使用，在hive中也有此函数，各位读者只需要注意入参的格式和数据类型即可。
除了LPAD函数，同样有RPAD函数，用法和LPAD一样，只不过它是向右补齐字符串到指定长度。