将日期格式化为文字

Question

我想展示一下各部门员工到工作二十周年纪念日的日期。这就是我所拥有的：

    select e.last_name, e.department_id, add_months(min(h.start_date), 240) as "20 Year Anniversary"
    from employees e
    inner join job_history h
    on h.employee_id = e.employee_id
    group by e.department_id, e.last_name;

这显示：

    Last_Name   Department_ID    20 Year Anniversary
  1 Hartstein              20    17-FEB-16
  2 Bob                    90    21-SEP-09

诸若此类。但是我想用这种格式显示日期:一月二十一号，八十二岁。我将如何安排日期？

Answer 1

我想这是你要找的。TO_CHAR date formatting.Check出甲骨文官方文档页面

01/server.102/b14200/functions180.htm

select e.last_name, e.department_id,to_char(add_months(min(h.start_date), 240),'Month fmDdsp, Year') as "20 Year Anniversary"
    from employees e
    inner join job_history h
    on h.employee_id = e.employee_id
    group by e.department_id, e.last_name;