Oracle PL/SQL/Data Type/INTERVAL

An assignment to var2

   
SQL>
SQL> DECLARE
  2    var2 INTERVAL YEAR(3) TO MONTH;
  3  BEGIN
  4      -- Shorthand for a 101 year and 3 month interval.
  5      var2 := "101-3";
  6  END;
  7  /
PL/SQL procedure successfully completed.

INTERVAL DAY TO SECOND

  
SQL>
SQL> declare
  2    t1   timestamp := current_timestamp + 1 ;
  3    i1  INTERVAL DAY TO SECOND;
  4  begin
  5    i1 := t1 - current_timestamp;
  6    dbms_output.put_line(i1);
  7  end;
  8  /
+00 23:59:59.640000
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

Shorthand for a 101 year and 3 month interval(INTERVAL "101-3" YEAR TO MONTH)

  

SQL>
SQL> DECLARE
  2    var2 INTERVAL YEAR(3) TO MONTH;
  3  BEGIN
  4      
  5      var2 := INTERVAL "101-3" YEAR TO MONTH;
  6  END;
  7  /
PL/SQL procedure successfully completed.

Shorthand for a 101 year and 3 month interval(INTERVAL "101" YEAR)

  
SQL>
SQL> DECLARE
  2    var2 INTERVAL YEAR(3) TO MONTH;
  3  BEGIN
  4      
  5      var2 := INTERVAL "101" YEAR;
  6  END;
  7  /
PL/SQL procedure successfully completed.

Shorthand for a 101 year and 3 month interval(INTERVAL "3" MONTH)

  
SQL>
SQL> DECLARE
  2    var2 INTERVAL YEAR(3) TO MONTH;
  3  BEGIN
  4      -- Shorthand for a 101 year and 3 month interval.
  5      var2 := INTERVAL "3" MONTH;
  6  END;
  7  /
PL/SQL procedure successfully completed.

This script demonstrates the use of the INTERVAL types

  
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     v_college_deadline TIMESTAMP;
  3  BEGIN
  4     v_college_deadline := TO_TIMESTAMP("06/06/2004", "DD/MM/YYYY") + INTERVAL "12-3" YEAR TO MONTH;
  5
  6     DBMS_OUTPUT.PUT_LINE("My daughter leaves for college in " ||v_college_deadline);
  7  END;
  8  /
My daughter leaves for college in 06-SEP-16 12.00.00.000000 AM
PL/SQL procedure successfully completed.