概述
71. Given below is a list of datetime data types and examples of values stored
in them in a random order:
Datatype Example
1)INTERVAL YEAR TO MONTH a) '20030415 8:00:00 8:00'
2)TIMESTAMP WITH LOCAL TIME ZONE b) '+06 03:30:16.000000'
3)TIMESTAMP WITH TIME ZONE c) '17JUN0312.00.00.000000 AM'
4)INTERVAL DAY TO SECOND d) '+0200'
Identify the option that correctly matches the data types with the values.
A. 1d,2c,3a,4b
B. 1b,2a,3c,4d
C. 1b,2a,3d,4c
D. 1d,2c,3b,4a
Answer: A
72. View the Exhibit and examine the description of the PRODUCT_INFORMATION
table.You want to display the expiration date of the warranty for a product.
Which SQL statement would you execute?
A. SELECT product_id, SYSDATE + warranty_period
FROM product_information;
B. SELECT product_id, TO_YMINTERVAL(warranty_period)
FROM product_information;
C. SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period
FROM product_information;
D. SELECT product_id, TO_YMINTERVAL(SYSDATE + warranty_period)
FROM product_information;
Answer: A
产品的到期时间
Interval Literals
An interval literal specifies a period of time. You can specify these differences in terms of years and months, or in terms of days, hours, minutes, and seconds. Oracle Database supports two types of interval literals, YEAR TO MONTH and DAY TO SECOND. Each type contains a leading field and may contain a trailing field. The leading field defines the basic unit of date or time being measured. The trailing field defines the smallest increment of the basic unit being considered. For example, a YEAR TO MONTH interval considers an interval of years to the nearest month. A DAY TO MINUTE interval considers an interval of days to the nearest minute.
If you have date data in numeric form, then you can use the NUMTOYMINTERVAL or NUMTODSINTERVAL conversion function to convert the numeric data into interval values.
Interval literals are used primarily with analytic functions.
See Also:
"Analytic Functions", NUMTODSINTERVAL, NUMTOYMINTERVAL, and Oracle Data Warehousing GuideINTERVAL YEAR TO MONTH
Specify YEAR TO MONTH interval literals using the following syntax:
interval_year_to_month::=
Description of the illustration interval_year_to_month.gif
where
-
'integer [-integer]'specifies integer values for the leading and optional trailing field of the literal. If the leading field isYEARand the trailing field isMONTH, then the range of integer values for the month field is 0 to 11. -
precisionis the maximum number of digits in the leading field. The valid range of the leading field precision is 0 to 9 and its default value is 2.
Restriction on the Leading Field If you specify a trailing field, it must be less significant than the leading field. For example, INTERVAL '0-1' MONTH TO YEAR is not valid.
The following INTERVAL YEAR TO MONTH literal indicates an interval of 123 years, 2 months:
INTERVAL '123-2' YEAR(3) TO MONTH
Examples of the other forms of the literal follow, including some abbreviated versions:
| Form of Interval Literal | Interpretation |
|---|---|
INTERVAL '123-2' YEAR(3) TO MONTH | An interval of 123 years, 2 months. You must specify the leading field precision if it is greater than the default of 2 digits. |
INTERVAL '123' YEAR(3) | An interval of 123 years 0 months. |
INTERVAL '300' MONTH(3) | An interval of 300 months. |
INTERVAL '4' YEAR | Maps to INTERVAL '4-0' YEAR TO MONTH and indicates 4 years. |
INTERVAL '50' MONTH | Maps to INTERVAL '4-2' YEAR TO MONTH and indicates 50 months or 4 years 2 months. |
INTERVAL '123' YEAR | Returns an error, because the default precision is 2, and '123' has 3 digits. |
You can add or subtract one INTERVAL YEAR TO MONTH literal to or from another to yield another INTERVAL YEAR TO MONTH literal. For example:
INTERVAL '5-3' YEAR TO MONTH + INTERVAL'20' MONTH = INTERVAL '6-11' YEAR TO MONTH
INTERVAL DAY TO SECOND
Specify DAY TO SECOND interval literals using the following syntax:
interval_day_to_second::=
Description of the illustration interval_day_to_second.gif
where
-
integerspecifies the number of days. If this value contains more digits than the number specified by the leading precision, then Oracle returns an error. -
time_exprspecifies a time in the formatHH[:MI[:SS[.n]]]orMI[:SS[.n]]orSS[.n], wherenspecifies the fractional part of a second. Ifncontains more digits than the number specified byfractional_seconds_precision, thennis rounded to the number of digits specified by thefractional_seconds_precisionvalue. You can specifytime_exprfollowing an integer and a space only if the leading field isDAY. -
leading_precisionis the number of digits in the leading field. Accepted values are 0 to 9. The default is 2. -
fractional_seconds_precisionis the number of digits in the fractional part of theSECONDdatetime field. Accepted values are 1 to 9. The default is 6.
Restriction on the Leading Field: If you specify a trailing field, it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid. As a result of this restriction, if SECOND is the leading field, the interval literal cannot have any trailing field.
The valid range of values for the trailing field are as follows:
-
HOUR: 0 to 23 -
MINUTE: 0 to 59 -
SECOND: 0 to 59.999999999
Examples of the various forms of INTERVAL DAY TO SECOND literals follow, including some abbreviated versions:
| Form of Interval Literal | Interpretation |
|---|---|
INTERVAL '4 5:12:10.222' DAY TO SECOND(3) | 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second. |
INTERVAL '4 5:12' DAY TO MINUTE | 4 days, 5 hours and 12 minutes. |
INTERVAL '400 5' DAY(3) TO HOUR | 400 days 5 hours. |
INTERVAL '400' DAY(3) | 400 days. |
INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) | 11 hours, 12 minutes, and 10.2222222 seconds. |
INTERVAL '11:20' HOUR TO MINUTE | 11 hours and 20 minutes. |
INTERVAL '10' HOUR | 10 hours. |
INTERVAL '10:22' MINUTE TO SECOND | 10 minutes 22 seconds. |
INTERVAL '10' MINUTE | 10 minutes. |
INTERVAL '4' DAY | 4 days. |
INTERVAL '25' HOUR | 25 hours. |
INTERVAL '40' MINUTE | 40 minutes. |
INTERVAL '120' HOUR(3) | 120 hours. |
INTERVAL '30.12345' SECOND(2,4) | 30.1235 seconds. The fractional second '12345' is rounded to '1235' because the precision is 4. |
You can add or subtract one DAY TO SECOND interval literal from another DAY TO SECOND literal. For example.
INTERVAL'20' DAY - INTERVAL'240' HOUR = INTERVAL'10-0' DAY TO SECOND
最后
以上就是微笑大山为你收集整理的Interval YEAR to MONTH的全部内容，希望文章能够帮你解决Interval YEAR to MONTH所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复