如何在Oracle查询中将日期字段拼接为DD-MM-YYYY格式？

现有Oracle表AUTH_USER_REGISTRATION，当前查询返回的dob字段格式为DD-英文月份-YYYY，需要调整为DD-MM-YYYY（两位数字月份）格式，同时正确处理空值情况。

以下是修改后的完整查询语句：

SELECT
    user_id as user_id,
    user_name as user_name,
    mail as user_mail,
    fullname as full_name,
    case 
        when upper(gender) in ('MALE', 'MAIL') then 'Male'
        when upper(gender) = 'FEMALE' then 'Female'
        else 'Others'
    end as gender,
    to_char(to_date(user_month, 'Month'), 'MM') as user_month,
    case 
        when user_day is not null and user_day <> '' 
             and user_month is not null and user_month <> '' 
             and user_year is not null and user_year <> ''
        then user_day || '-' || to_char(to_date(user_month, 'Month'), 'MM') || '-' || user_year
        else '' 
    end as dob,
    created,
    status as status,
    uu_id as uuid,
    mobile as mobile,
    changed 
FROM
    auth_user_registration;

关键修改说明 #

• 月份转换优化：用to_date(user_month, 'Month')将英文月份转换为日期类型，再通过to_char(..., 'MM')提取两位数字月份，比原有的CASE语句更简洁，自动兼容大小写（如march或MARCH均可识别），同时修正了原CASE中Ocotober的拼写错误。
• 空值判断修正：将原查询中的OR逻辑改为AND，确保日、月、年三个字段都不为空且非空字符串时才拼接出生日期，否则返回空字符串，避免无效的半值拼接。
• 性别判断简化：通过upper(gender)统一转换为大写后匹配，减少重复判断逻辑，提升代码可读性和执行效率。

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