I am using ROC era system as my date format (ex. 1031120 represents 20141120).
If I would like to create an Oracle function to do add days just like the Oracle build-in function ADD_MONTHS.
Solution
Here is my tailor-made Oracle function.
ADD_DAYS returns the date date plus integer days.
- The MINGUO_YYYMMDD argument only accept ROC era system as date format, ex. 1031120.
- The integer argument, DAYS, can be an integer or any value that can be implicitly converted to an integer.
- The return type is always VARCHAR2 with ROC era system as date format.
- If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | create or replace FUNCTION ADD_DAYS ( MINGUO_YYYMMDD IN VARCHAR2 --CHINESE YEAR , DAYS IN NUMBER --5 MEANS ADD 5 DAYS, -3 MEANS SUBSTRACT 3 DAYS ) RETURN VARCHAR2 IS NEW_MINGUO_YYY_MM_DD VARCHAR2(7); --RETURN RESULT BEGIN SELECT NEW_MINGUO_YYY_MM_DD INTO NEW_MINGUO_YYY_MM_DD FROM( SELECT SUBSTR(TO_CHAR(TO_DATE(SUBSTR(MINGUO_YYYMMDD, 0, 3)+1911 || SUBSTR(MINGUO_YYYMMDD, 4, 4), 'YYYYMMDD')+DAYS, 'YYYYMMDD'), 0, 4)-1911 || SUBSTR(TO_CHAR(TO_DATE(SUBSTR(MINGUO_YYYMMDD, 0, 3)+1911 || SUBSTR(MINGUO_YYYMMDD, 4, 4), 'YYYYMMDD')+DAYS, 'YYYYMMDD'), 5, 4) AS NEW_MINGUO_YYY_MM_DD FROM DUAL ); RETURN NEW_MINGUO_YYY_MM_DD; END ADD_DAYS; |
Test
Add 5 days
1 | SELECT ADD_DAYS('1031120', 5) FROM DUAL; |
Subtract 3 days
1 | SELECT ADD_DAYS('1030803', -3) FROM DUAL; |
