如何筛选仅包含已结束月份的数据（日期格式为YYYY-MM）

你已创建如下临时视图生成近三个月的年月数据：

create or replace temporary view dates as
   select date_format(add_months(now(), -2), 'yyyy-MM') as date union
   select date_format(add_months(now(), -1), 'yyyy-MM') as date union
   select date_format(add_months(now(), 0), 'yyyy-MM') as date
;

执行 select * from dates 后得到结果：

需求：仅返回已结束的完整月份数据。例如当当前月份为2022-10时，查询结果应仅包含2022-08和2022-09。

解决方案 #

可以通过将字符串格式的年月转换为日期对象，再与当前月份的起始日期对比来实现筛选：

select * from dates
where to_date(concat(date, '-01')) < date_trunc('month', current_date())

逻辑说明 #

1. concat(date, '-01') 将年月字符串拼接为当月第一天的日期字符串（如2022-08变为2022-08-01）
2. to_date() 将拼接后的字符串转换为日期类型
3. date_trunc('month', current_date()) 获取当前月份的第一天（如当前日期是2022-10-xx时，结果为2022-10-01）
4. 小于当前月份第一天的日期，对应的自然是已经完全结束的月份

如果你的SQL方言不支持date_trunc，也可以用add_months和date_format生成当前月份的年月字符串，直接做字符串对比：

select * from dates
where date < date_format(add_months(current_date(), -day(current_date()) + 1), 'yyyy-MM')

针对支持last_day函数的SQL环境，还可以用更直观的写法：

select * from dates
where to_date(concat(date, '-01')) <= last_day(add_months(current_date(), -1))

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