SQLite中如何比较DD-MMM-YYYY格式日期并查询指定及相邻日期行

嘿，这个问题我碰到过好几次了！SQLite不像MySQL、PostgreSQL这类数据库那样内置了直接解析英文月份缩写日期的函数，所以咱们得先把这种非标准格式的日期转换成SQLite能识别的标准日期，之后才能轻松做比较和范围筛选。我来一步步给你拆解：

第一步：把DD-MMM-YYYY字符串转成标准日期 #

直接比较这种格式的字符串会出问题——比如"31-DEC-2019"和"01-JAN-2020"，按字符串排序会觉得后者更大，但实际日期却是前者更早。所以必须先把它转换成YYYY-MM-DD格式的日期值，这一步需要用substr提取年、月、日部分，再用CASE把月份缩写转成数字：

-- 假设你的日期列叫event_date，转换逻辑如下
date(
  substr(event_date, 8, 4) || '-' || -- 提取年份：从第8位开始取4个字符
  case upper(substr(event_date, 4, 3)) -- 提取月份缩写，转成大写避免大小写问题
    when 'JAN' then '01'
    when 'FEB' then '02'
    when 'MAR' then '03'
    when 'APR' then '04'
    when 'MAY' then '05'
    when 'JUN' then '06'
    when 'JUL' then '07'
    when 'AUG' then '08'
    when 'SEP' then '09'
    when 'OCT' then '10'
    when 'NOV' then '11'
    when 'DEC' then '12'
  end || '-' ||
  substr(event_date, 1, 2) -- 提取日期：从第1位开始取2个字符
)

用date()函数包裹后，就能得到SQLite可以正常处理的日期类型了。

第二步：筛选指定日期及其相邻日期 #

有了标准日期后，筛选指定日期（比如10-OCT-2017）和它的前一天、后一天就很简单了。这里推荐用CTE（WITH子句）来简化代码，避免重复写转换逻辑：

WITH converted_dates AS (
  -- 先把表中所有日期转成标准格式
  SELECT *,
         date(
           substr(event_date, 8, 4) || '-' ||
           case upper(substr(event_date, 4, 3))
             when 'JAN' then '01'
             when 'FEB' then '02'
             when 'MAR' then '03'
             when 'APR' then '04'
             when 'MAY' then '05'
             when 'JUN' then '06'
             when 'JUL' then '07'
             when 'AUG' then '08'
             when 'SEP' then '09'
             when 'OCT' then '10'
             when 'NOV' then '11'
             when 'DEC' then '12'
           end || '-' ||
           substr(event_date, 1, 2)
         ) AS standard_date
  FROM your_table -- 替换成你的表名
),
target_date AS (
  -- 把指定的目标日期也转成标准格式
  SELECT date(
           substr('10-OCT-2017', 8, 4) || '-' ||
           case upper(substr('10-OCT-2017', 4, 3))
             when 'JAN' then '01'
             when 'FEB' then '02'
             when 'MAR' then '03'
             when 'APR' then '04'
             when 'MAY' then '05'
             when 'JUN' then '06'
             when 'JUL' then '07'
             when 'AUG' then '08'
             when 'SEP' then '09'
             when 'OCT' then '10'
             when 'NOV' then '11'
             when 'DEC' then '12'
           end || '-' ||
           substr('10-OCT-2017', 1, 2)
         ) AS target
)
-- 筛选目标日期前后一天的所有行
SELECT cd.*
FROM converted_dates cd, target_date td
WHERE cd.standard_date BETWEEN date(td.target, '-1 day') AND date(td.target, '+1 day');

小提示 #

• 如果你的日期字符串里的月份缩写是小写的（比如10-oct-2017），upper()函数能帮你统一转换成大写，避免CASE匹配失败。
• 也可以直接用td.target - 1和td.target + 1来表示前后一天，SQLite会自动处理日期的加减。

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