Oracle 19c中基于YYMMDD格式STATUS_DATE获取近30天数据的问题

问题背景 #

• 使用Oracle 19c企业版
• STATUS_DATE字段存储格式为1位前缀+YYMMDD（例如值1230509，去掉前缀后是230509，对应2023年5月9日）
• 需求是获取当前日期往前30天内的数据，但多次尝试SQL均无输出，以下是当前使用的SQL：

select ul.loginname,ul.last_login_time,AU.CREATED_USER_ID,AU.SITE_ID,AU.ACCOUNT_NUMBER      
from phone_tools.PT_USER_LOGIN UL
INNER JOIN ALL_CUST_SRV_CATEGORIES@PPTOOLS_DBLINK_PSTAGE.WORLD AU ON AU.CREATED_USER_ID = UL.LOGINNAME              
-- where TO_CHAR(UL.last_login_time, 'YYYYMMDD') = TO_CHAR(SYSDATE-5, 'YYYYMMDD');
-- where AU.STATUS_DATE = 230509
-- WHERE TO_CHAR(SUBSTR(AU.STATUS_DATE,2) , 'DD') = TO_CHAR(SYSDATE-5, 'DD');
where --AU.STATUS_DATE >= TO_NUMBER(
          --  '1' 
           --  || LPAD(substr(TO_CHAR(EXTRACT (year FROM sysdate -30)),-2),2,'0') 
           -- || LPAD(TO_CHAR(EXTRACT (month FROM sysdate -30)),2,'0')
           -- || LPAD(TO_CHAR(EXTRACT (day FROM sysdate -30)),2,'0')
        --)
        -- End date
        --AND 
        AU.STATUS_DATE <= TO_NUMBER(
            '1' 
           -- || LPAD(substr(TO_CHAR(EXTRACT (year FROM sysdate -30)),-2),2,'0')
           -- || LPAD(TO_CHAR(EXTRACT (month FROM sysdate -30)),2,'0')
            || LPAD(TO_CHAR(EXTRACT (day FROM sysdate -30)),2)
        )

现有SQL的错误点 #

1. WHERE条件逻辑完全失效：你注释掉了起始日期的判断，只保留了AU.STATUS_DATE <= 拼接值，但拼接代码只保留了前缀1加上日部分，缺少年和月（比如sysdate-30的日是09，拼接后是109），这和STATUS_DATE的1YYMMDD格式完全不匹配，数值上远小于实际的STATUS_DATE值，自然查不到数据。
2. 日期拼接方式冗余且易出错：用EXTRACT拆分年、月、日再拼接的方式太复杂，还容易出现补零错误（比如月份是5月，直接EXTRACT得到的是5，没补零的话就变成123509，格式错误）。
3. 硬编码前缀缺乏通用性：假设前缀不一定是1，这种硬拼的方式会直接失效，而且没有从根本上解决日期比较的问题。

正确的查询方案 #

核心思路是把STATUS_DATE转换为标准日期类型，再和SYSDATE-30到SYSDATE的范围比较，这样逻辑清晰且不易出错：

SELECT ul.loginname, ul.last_login_time, AU.CREATED_USER_ID, AU.SITE_ID, AU.ACCOUNT_NUMBER
FROM phone_tools.PT_USER_LOGIN UL
INNER JOIN ALL_CUST_SRV_CATEGORIES@PPTOOLS_DBLINK_PSTAGE.WORLD AU 
    ON AU.CREATED_USER_ID = UL.LOGINNAME
WHERE 
    -- 截取STATUS_DATE后6位，转换为日期（Oracle默认将YY映射为20YY）
    TO_DATE(SUBSTR(AU.STATUS_DATE, 2), 'YYMMDD') BETWEEN TRUNC(SYSDATE - 30) AND TRUNC(SYSDATE)

方案说明 #

• 用SUBSTR(AU.STATUS_DATE, 2)直接提取后面6位的日期部分，不管前缀是什么都不影响
• TO_DATE(..., 'YYMMDD')将字符串转换为标准日期，直接支持范围比较
• TRUNC函数去掉日期的时间部分，确保包含当天的所有数据

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