Oracle PL/SQL Tutorial - PL/SQL INTERVAL

• « Previous
• Next »

INTERVAL YEAR TO MONTH Data Type

INTERVAL YEAR TO MONTH stores and manipulates intervals of years and months.

The syntax is:

 INTERVAL YEAR[(precision)] TO MONTH 
 

precision specifies the number of digits in the years field.

We must use an integer literal in the range 0..4. The default is 2.

The following code shows how to Assign Literals to an INTERVAL YEAR TO MONTH Variable.

DECLARE -- w  w w  . j  a  v a2s  .  c  om
  lifetime  INTERVAL YEAR(3) TO MONTH; 
BEGIN 
  lifetime := INTERVAL '101-3' YEAR TO MONTH;  -- Interval literal 
 
  lifetime := '101-3';  -- Implicit conversion from character type 
 
  lifetime := INTERVAL '101' YEAR;  -- Specify only years 
  lifetime := INTERVAL '3' MONTH;   -- Specify only months 
END; 
/ 

INTERVAL DAY TO SECOND Data Type

INTERVAL DAY TO SECOND stores and manipulates intervals of days, hours, minutes, and seconds.

The syntax is:

INTERVAL DAY[(leading_precision) TO SECOND (fractional_seconds_precision) 

leading_precision and fractional_seconds_precision specify the number of digits in the days field and seconds field, respectively.

We can use an integer literal in the range 0..9.

The defaults are 2 and 6, respectively.

The following code declares a variable of type INTERVAL DAY TO SECOND and assigns a value to it.

DECLARE --  w  w w . j av  a2  s  .  co  m
  lag_time  INTERVAL DAY(3) TO SECOND(3); 
BEGIN 
  lag_time := '7 09:24:30'; 
 
  IF lag_time > INTERVAL '6' DAY THEN 
    DBMS_OUTPUT.PUT_LINE ('Greater than 6 days'); 
  ELSE 
    DBMS_OUTPUT.PUT_LINE ('Less than 6 days'); 
  END IF; 
END; 
/ 

The code above generates the following result.

Datetime and Interval Arithmetic

PL/SQL can create datetime and interval expressions.

The following table shows the operators that we can use:

• Next »
• « Previous