Tim Tim - 24 days ago 7
SQL Question

Formating Dates into Words

I want to display the dates when the employees in the departments reach their twenty year working anniversary. This is what I have:

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;


This displays:

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


And so on. But I want to display the dates in this format: January twenty-one, Nineteen Eighty-Two. How would I format the dates?

Answer

I think this is you are looking for. TO_CHAR date formatting.Check out official doc page of oracle

http://docs.oracle.com/cd/B19306_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;
Comments