NUMTODSINTERVAL

NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal.

The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value.
The argument interval_unit can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype.
The value for interval_unit specifies the unit of n and must resolve to one of the following string values:

‘DAY’

‘HOUR’

‘MINUTE’

‘SECOND’

CREATE OR REPLACE FUNCTION FN_ADD_TIMESTAMP(
       P_TIMESTAMP IN TIMESTAMP,
       P_AMOUNT IN NUMBER,
       P_UNITS IN VARCHAR2)
       RETURN TIMESTAMP
    IS
       C_DATE CONSTANT DATE := CAST(P_TIMESTAMP AS DATE);
       L_TIMESTAMP TIMESTAMP;
    BEGIN
       CASE
          WHEN P_UNITS IN ('SECOND', 'MINUTE', 'HOUR', 'DAY') THEN
             L_TIMESTAMP := P_TIMESTAMP + NUMTODSINTERVAL(P_AMOUNT, P_UNITS);
          WHEN P_UNITS = 'WEEK' THEN
             L_TIMESTAMP := P_TIMESTAMP +
                NUMTODSINTERVAL(P_AMOUNT * 7, 'DAY');
          WHEN P_UNITS = 'MONTH' THEN
             L_TIMESTAMP := P_TIMESTAMP +
                NUMTODSINTERVAL(ADD_MONTHS(C_DATE, P_AMOUNT) - C_DATE, 'DAY');
          WHEN P_UNITS = 'YEAR' THEN
             L_TIMESTAMP := P_TIMESTAMP +
                NUMTODSINTERVAL(ADD_MONTHS(C_DATE, P_AMOUNT * 12) - C_DATE, 'DAY');
       END CASE;

       RETURN L_TIMESTAMP;
    END FN_ADD_TIMESTAMP;
/

SQL> SELECT TO_TIMESTAMP(TRUNC(SYSDATE + 1),’DD/MM/RRRR HH:MI:SS’) + numtodsinterval(30,’MINUTE’) FROM DUAL;

TO_TIMESTAMP(TRUNC(SYSDATE+1),’DD/MM/RRRRHH:MI:SS’)+NUMTODSINTERVAL(30,’MIN
—————————————————————————
02-AUG-14 12.30.00.000000000 AM

SQL> select fn_add_timestamp(TO_TIMESTAMP(TRUNC(SYSDATE + 1),’DD/MM/RRRR HH:MI:SS’),30,’MINUTE’) from dual;

FN_ADD_TIMESTAMP(TO_TIMESTAMP(TRUNC(SYSDATE+1),’DD/MM/RRRRHH:MI:SS’),30,’MI
—————————————————————————
02-AUG-14 12.30.00.000000000 AM

http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions108.htm#SQLRF00682
http://www.techonthenet.com/oracle/functions/numtodsinterval.php

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s