Oracle SQL如何实现月初到当前日及自定义起止区间的数据查询

可以在单条SQL中实现上述需求，我们通过可空的自定义日期参数配合默认逻辑判断即可实现，无需拆分多条语句。

参数设计 #

我们定义两个可空的输入参数：

• :p_start_date：自定义起始日期，输入格式为dd/mm/yyyy，比如22/10/2014，不传值则走默认查询规则
• :p_end_date：自定义结束日期，输入格式同上，不传值则走默认查询规则

改写后SQL（兼容时分秒场景，推荐） #

SELECT COUNT(EMP_ID) 
FROM EMPLOYEE
WHERE created_date >= 
  CASE
    -- 传入自定义起始日期则直接转换使用
    WHEN :p_start_date IS NOT NULL THEN TO_DATE(:p_start_date, 'dd/mm/yyyy')
    -- 未传参数时走默认逻辑：当前是当月1号则取上个月1号，否则取当月1号
    WHEN TRUNC(SYSDATE) = TRUNC(SYSDATE, 'mm') THEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1)
    ELSE TRUNC(SYSDATE, 'mm')
  END
AND created_date <
  CASE
    -- 传入自定义结束日期则取日期+1天，避免created_date带时分秒时漏数据
    WHEN :p_end_date IS NOT NULL THEN TO_DATE(:p_end_date, 'dd/mm/yyyy') + 1
    -- 未传参数时走默认逻辑：当前是当月1号则取当月1号（即上个月最后一天的下一天），否则取当前日期+1天
    WHEN TRUNC(SYSDATE) = TRUNC(SYSDATE, 'mm') THEN TRUNC(SYSDATE, 'mm')
    ELSE TRUNC(SYSDATE) + 1
  END;

逻辑说明 #

1. 自定义查询适配：只要两个参数传入有效值，就直接按指定的日期区间查询，完全符合自定义查询要求
2. 默认规则适配：
   • 若当前系统日期不是当月1日（比如2018年1月11日）：查询范围自动匹配当月1日到当前系统日期
   • 若当前系统日期是当月1日（比如2018年2月1日）：查询范围自动匹配上个月整月（2018年1月1日到1月31日）
3. 写法优化：使用>= 起始日 且 < 结束日+1天替代BETWEEN，可以避免created_date带时分秒时，漏掉结束日当天零点之后的数据，兼容性更强。

与原逻辑一致的BETWEEN写法（仅适配不带时分秒的created_date） #

如果需要和你原有SQL的BETWEEN逻辑保持一致，可以用以下版本：

SELECT COUNT(EMP_ID) 
FROM EMPLOYEE
WHERE created_date BETWEEN
  CASE
    WHEN :p_start_date IS NOT NULL THEN TO_DATE(:p_start_date, 'dd/mm/yyyy')
    WHEN TRUNC(SYSDATE) = TRUNC(SYSDATE, 'mm') THEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1)
    ELSE TRUNC(SYSDATE, 'mm')
  END
AND
  CASE
    WHEN :p_end_date IS NOT NULL THEN TO_DATE(:p_end_date, 'dd/mm/yyyy')
    WHEN TRUNC(SYSDATE) = TRUNC(SYSDATE, 'mm') THEN ADD_MONTHS(TRUNC(SYSDATE, 'mm'), -1) + INTERVAL '1' MONTH - INTERVAL '1' SECOND
    ELSE TRUNC(SYSDATE)
  END;

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