Oracle SQL查询30岁以下人员：DDMMYY格式出生日期世纪适配问题

没问题，咱们可以直接基于你给出的挪威社保号规则来给出生日期补上世纪信息，避开无世纪日期的歧义问题，同时用更准确的方式计算年龄筛选30岁以下的人员。下面是具体的解决步骤和完整查询示例：

1. 基于社保号规则构造带世纪的完整出生日期 #

根据你提供的规则：当社保号PERSON_NR前三位大于499且长度为5时，出生日期属于2000年后，其他情况默认属于1900-1999年。我们可以用CASE语句拼接出完整的DDMMYYYY格式字符串：

CASE
    WHEN SUBSTR(PERSON_NR, 1, 3) > 499 AND LENGTH(PERSON_NR) = 5
        THEN SUBSTR(BIRTH_DATE_FROM_DB, 1, 4) || '20' || SUBSTR(BIRTH_DATE_FROM_DB, 5, 2)
    ELSE
        SUBSTR(BIRTH_DATE_FROM_DB, 1, 4) || '19' || SUBSTR(BIRTH_DATE_FROM_DB, 5, 2)
END AS FULL_BIRTH_DATE_STR

这里解释下：BIRTH_DATE_FROM_DB是DDMMYY格式，前4位是DDMM，后2位是YY，我们根据规则拼接20或19，得到带世纪的完整日期字符串。

2. 将完整日期字符串转换为DATE类型 #

把上面的字符串用TO_DATE函数转换成Oracle可识别的日期类型，指定格式掩码为'DDMMYYYY'：

TO_DATE(
    CASE
        WHEN SUBSTR(PERSON_NR, 1, 3) > 499 AND LENGTH(PERSON_NR) = 5
            THEN SUBSTR(BIRTH_DATE_FROM_DB, 1, 4) || '20' || SUBSTR(BIRTH_DATE_FROM_DB, 5, 2)
        ELSE
            SUBSTR(BIRTH_DATE_FROM_DB, 1, 4) || '19' || SUBSTR(BIRTH_DATE_FROM_DB, 5, 2)
    END,
    'DDMMYYYY'
) AS FULL_BIRTH_DATE

3. 准确计算年龄并筛选30岁以下人员 #

不建议用天数相减的方式（闰年、不同月份天数差异会导致误差），推荐用MONTHS_BETWEEN函数计算周岁年龄，再取整数部分：

TRUNC(MONTHS_BETWEEN(SYSDATE, FULL_BIRTH_DATE) / 12) < 30

这个方法会自动处理闰年和月份天数的差异，计算出更精准的周岁年龄。

把以上步骤整合起来，最终的查询语句如下：

SELECT *
FROM YOUR_TABLE_NAME
WHERE TRUNC(MONTHS_BETWEEN(
    SYSDATE,
    TO_DATE(
        CASE
            WHEN SUBSTR(PERSON_NR, 1, 3) > 499 AND LENGTH(PERSON_NR) = 5
                THEN SUBSTR(BIRTH_DATE_FROM_DB, 1, 4) || '20' || SUBSTR(BIRTH_DATE_FROM_DB, 5, 2)
            ELSE
                SUBSTR(BIRTH_DATE_FROM_DB, 1, 4) || '19' || SUBSTR(BIRTH_DATE_FROM_DB, 5, 2)
        END,
        'DDMMYYYY'
    )
) / 12) < 30;

• 如果BIRTH_DATE_FROM_DB是数字类型，需要先转成字符类型：TO_CHAR(BIRTH_DATE_FROM_DB, 'FM000000')（用FM去掉前导空格，确保是6位的DDMMYY格式）。
• 你之前的写法里，TO_DATE(SYSDATE, 'MM-DD-YYYY')是多余的，SYSDATE本身就是Oracle的日期类型，直接使用即可，否则可能因会话的日期格式设置导致转换错误。
• 可以把日期转换逻辑封装成自定义函数，方便重复使用：

CREATE OR REPLACE FUNCTION GET_FULL_BIRTH_DATE(p_birth_date IN VARCHAR2, p_person_nr IN VARCHAR2)
RETURN DATE
IS
    v_full_date_str VARCHAR2(8);
BEGIN
    IF SUBSTR(p_person_nr, 1, 3) > 499 AND LENGTH(p_person_nr) = 5 THEN
        v_full_date_str := SUBSTR(p_birth_date, 1, 4) || '20' || SUBSTR(p_birth_date, 5, 2);
    ELSE
        v_full_date_str := SUBSTR(p_birth_date, 1, 4) || '19' || SUBSTR(p_birth_date, 5, 2);
    END IF;
    RETURN TO_DATE(v_full_date_str, 'DDMMYYYY');
END;
/

简化后的查询：

SELECT *
FROM YOUR_TABLE_NAME
WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, GET_FULL_BIRTH_DATE(BIRTH_DATE_FROM_DB, PERSON_NR)) / 12) < 30;

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