How to convert year from Western to Chinese in Oracle? (在Oracle中如何將西元年轉民國年)

Requirement
Assume we had a table which contains all work days, and recorded in CALENDAR_DATE column with date data type.

According customers' requirement, they would to show Chinese year instead of Western year.

Solution
We can make good use of EXTRACT function which provide by Oracle.

SQL statement:

1:  SELECT CALENDAR_DATE,   
2:      TRIM(TO_CHAR(EXTRACT(YEAR FROM CALENDAR_DATE)-1911, '000'))||  
3:      TRIM(TO_CHAR(EXTRACT(MONTH FROM CALENDAR_DATE), '00'))||  
4:      TRIM(TO_CHAR(EXTRACT(DAY FROM CALENDAR_DATE), '00'))   
5:      AS CHINESE_CALENDAR  
6:   FROM SYS111FC  
7:  WHERE NO_WORK_IND = '0' AND ROC_YR='102'  

After executing the SQL as above, we can see 2013 had been convert to 102.