These intervals from Table 11.6 can be tested by
substituting them into the following query.
The month syntax is converted into a
years and months value.
select
interval 20 month
from
dual
;
INTERVAL'20'MONTH
--------------------------------------------------------------------
+01-08
A YEAR TO MONTH interval can be added to or
subtracted from with the result being another
YEAR TO MONTH interval.
select
interval 1 year interval 1
month
from
dual
;
INTERVAL'1'YEAR-INTERVAL'1'MONTH
--------------------------------------------------------------------
+000000000-11
The following examples relate to the DAY TO
SECOND interval literal syntax.
As with the previous example, if a
trailing field is specified, it must be less
significant than the previous field.
DAY
- Number of days with a default precision of two
digits
HOUR
- Number of hours with a default precision of
three digits.
If specified as a trailing field, it has
allowable values of zero to 23.
MINUTE
- Number of minutes with a default precision of
five digits.
If specified as a trailing field, it has
allowable values of zero to 59.
SECOND
- Number of seconds with a default precision of
seven digits before the decimal point and six
digits after.
If specified as a trailing field, it has
allowable values of zero to 59.999999999.
|
INTERVA LITERAL
|
MEANING
|
|
INTERVAL 2 3:04:11.333 DAY TO
SECOND(3)
|
2 days, 3 hours, 4 minutes, 11 seconds
and 333 thousandths of a second
|
|
INTERVAL 2 3:04 DAY TO MINUTE
|
2 days, 3 hours, 4 minutes
|
|
INTERVAL 2 3 DAY TO HOUR
|
2 days, 3 hours
|
|
INTERVAL 2 DAY
|
2 days
|
|
INTERVAL 03:04:11.333 HOUR TO SECOND
|
3 hours, 4 minutes, 11 seconds and 333
thousandths of a second
|
|
INTERVAL 03:04 HOUR TO MINUTE
|
3 hours, 4 minutes
|
|
INTERVAL 40 HOUR
|
40 hours
|
|
INTERVAL 04:11.333 MINUTE TO SECOND
|
4 minutes, 11 seconds and 333
thousandths of a second
|
|
INTERVAL 70 MINUTE
|
70 minutes
|
|
INTERVAL 70 SECOND
|
70 seconds
|
|
INTERVAL 03:70 HOUR TO MINUTE
|
Error produced.
When the leading field is
specified, the allowable values for the
trailing field must be within normal
range.
|
Table 11.7 -
DAY TO SECOND Intervals and Their Meanings
Substituting the intervals from Table 11.7 into
the following query will allow those intervals
to be tested.
The default precision for seconds is used
because it has not been to three decimal places.
select
interval 2 3:04:11.333 day to
second
from
dual
;
INTERVAL'23:04:11.333'DAYTOSECOND
--------------------------------------------------------------------
+02 03:04:11.333000
A DAY TO SECOND
interval can be added to or subtracted from with
the result being another DAY TO SECOND interval.
select
interval 1 day interval 1
second
from
dual
;
INTERVAL'1'DAY-INTERVAL'1'SECOND
--------------------------------------------------------------------
+000000000 23:59:59.000000000
Intervals
can also be combined with dates to manipulate
date values. The following query shows how this
is done:
select
sysdate,
sysdate + interval 1 month +
interval 1 day interval 3 second
from
dual
;
SYSDATE
SYSDATE+INTERVAL'1'M
--------------------
--------------------
10-JUL-2004
19:55:53 11-AUG-2004 19:55:50
Now that the groundwork for using PL/SQL
expressions has been covered, the calendaring
syntax available in Oracle will be presented in
the following section.
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
