Oracle Syntax:
Interval 'integer [-integer] '{year | month} [(precision)] [
{Year | month}]This data type is often used to indicate a time difference. Note that the time difference is accurate only to the year and month. Precision is the exact domain of the year or month,
Valid range: 0 to 9. Default Value: 2. Eg:
Interval '1970-2' year (3) to month
Indicates:
"Year (3)" indicates that the accuracy of the year is 3. It can be seen that "123" is just 3 as a valid value. If the year (N ), if n is less than 3, an error occurs. Note that the default value is 2. Interval '000000' year (3)
Indicates 0 months from January 1, 123. Interval '000000' month (3)
300 months. Note that the precision of the month is 3. Interval '4' year
Indicates four years, the same
Interval '4-0' year to month is the same Interval '50' month
Indicates 50 months,
Same as interval '4-2' year to month Interval '000000' year
Indicates:
This field indicates that there is an error. The accuracy of 123 is 3, but the default value is 2. Therefore, interval '000000' year (3) should be written here)
Or "3" to a value greater than 3 and less than or equal to 9. Interval '5-3 'year to month + interval '20' month
=
Interval '6-11' year to month
Indicates 5 years, 3 months, and 20 months = 6 years, 11 months Functions related to this type:
Numtodsinterval (n, 'interval _ unit ')
Converts n to the value specified by interval_unit. interval_unit can be: day, hour, minute, second.
Note that this function cannot be converted to year or month. Numtoyminterval (n, 'interval _ unit ')
Interval_unit can be: year, month Eg: (Oracle version 9204, RedHat Linux
9.0)
SQL> select numtodsinterval (100, 'day') from dual; Numtodsinterval (100, 'day ')
---------------------------------------------------------------------------
+ 000000100
00:00:00. 000000000 SQL> C/day/second
1 * select numtodsinterval (100, 'second') from
Dual
SQL>/ Numtodsinterval (100, 'second ')
---------------------------------------------------------------------------
+ 000000000
00:01:40. 000000000 SQL> C/Second/minute
1 * select numtodsinterval (100, 'minute ') from
Dual
SQL>/ Numtodsinterval (100, 'minute ')
---------------------------------------------------------------------------
+ 000000000
01:40:00. 000000000 SQL> C/minute/hour
1 * select numtodsinterval (100, 'hour') from
Dual
SQL>/ Numtodsinterval (100, 'hour ')
---------------------------------------------------------------------------
+ 000000004
04:00:00. 000000000 SQL> C/hour/year
1 * select numtodsinterval (100, 'Year') from dual
SQL>/
Select numtodsinterval (100, 'Year') from dual
*
Error
At Line 1:
ORA-01760: Illegal argument for Function SQL> select
Numtoyminterval (100, 'Year') from dual; Numtoyminterval (100, 'Year ')
---------------------------------------------------------------------------
+ 000000100-00 SQL> C/year/month
1 * select numtoyminterval (100, 'month') from
Dual
SQL>/ Numtoyminterval (100, 'month ')
---------------------------------------------------------------------------
+ 000000008-04 Time calculation:
SQL> select to_date ('2017-12-12 ', 'yyyy-mm-dd ')-
To_date ('2017-12-01 ', 'yyyy-mm-dd') from dual; To_date ('2017-12-12 ', 'yyyy-MM-DD')-to_date ('2017-12-01 ', 'yyyy-MM-DD ')
---------------------------------------------------------------------
11
-- The result of subtraction is a day. SQL> C/1999-12-12/1999-01-12
1 * select to_date ('1970-01-12 ', 'yyyy-mm-dd ')-
To_date ('2017-12-01 ', 'yyyy-mm-dd') from dual
SQL>/ To_date ('2017-01-12 ', 'yyyy-MM-DD')-to_date ('2017-12-01 ', 'yyyy-MM-DD ')
---------------------------------------------------------------------
-323
-- It can also be a negative number. SQL> C/1999-01-12/2999-10-12
1 *
Select to_date ('1970-10-12 ', 'yyyy-mm-dd')-to_date ('1970-12-01', 'yyyy-mm-dd ')
From dual
SQL>/ To_date ('2017-10-12 ', 'yyyy-MM-DD')-to_date ('2017-12-01 ', 'yyyy-MM-DD ')
---------------------------------------------------------------------
365193 Next let's take a look at how to use interval year to month.
SQL> Create
Table BB (a date, B Date, C interval year (9) to month ); Table created. SQL> DESC BB;
Name null?
Type
-------------------------------------------------
----------------------------
A date
B Date
C interval year (9) to month SQL> insert into BB values (to_date ('1970-12-12 ', 'yyyy-mm-dd '),
To_date ('2017-12-01 ', 'yyyy-mm-dd'), null) 1 row created. SQL>
Select * from BB; A
B
---------
---------
C
---------------------------------------------------------------------------
12-dec-85
01-dec-84 SQL> Update BB set C = numtoyminterval (a-B, 'Year '); 1
Row updated. SQL> select * from BB; A
B
---------
---------
C
---------------------------------------------------------------------------
12-dec-85
01-dec-84
+ 000000376-00 -- Directly change the subtracted days to an adult, Because I specify to change the days to years.
SQL> select a-B, c from BB; A-B
----------
C
---------------------------------------------------------------------------
376
+ 000000376-00 SQL> insert into bb
Values (null, null, numtoyminterval (376, 'month ')); 1 row created. SQL> select * from BB; A B
C
---------
-----------------------------------------------------
12-dec-85
01-dec-84
+ 000000376-00
+ 000000031-04 SQL> insert
Into BB values (null, null, numtoyminterval (999999999, 'Year ')); 1 row
Created. SQL> select * from BB; A B
C
------------------
---------------------------------------------------------------------
12-dec-85 01-dec-84
+ 000000376-00
+ 000000031-04
+ 999999999-00 |
