Databases Reference
In-Depth Information
will reach or has reached the 250-month mark. For this result, he will use the
NUMTOYMINTERVAL function to add 250 months to the hire date:
select ename, hiredate, hiredate +
numtoyminterval(250,'month') "250 Months" from emp;
ENAME HIREDATE 250 Month
---------- --------- ---------
SMITH 17-DEC-80 17-OCT-01
ALLEN 20-FEB-81 20-DEC-01
WARD 22-FEB-81 22-DEC-01
JONES 02-APR-81 02-FEB-02
MARTIN 28-SEP-81 28-JUL-02
BLAKE 01-MAY-81 01-MAR-02
CLARK 09-JUN-81 09-APR-02
SCOTT 19-APR-87 19-FEB-08
KING 17-NOV-81 17-SEP-02
TURNER 08-SEP-81 08-JUL-02
ADAMS 23-MAY-87 23-MAR-08
JAMES 03-DEC-81 03-OCT-02
FORD 03-DEC-81 03-OCT-02
MILLER 23-JAN-82 23-NOV-02
14 rows selected.
Scott could have used the function TO_YMINTERVAL('20-10') to add 20
years and 10 months (250 months total) to the hire date. Whether to use one
method or another depends on how you want to specify the format—as a dis-
crete number of months or years or as a combination of months and years.
Now that Scott knows more about the conversion functions, he wants to
revisit one of the queries he wrote previously:
select ename, sal, round(sqrt(sal),2) "Bonus" from emp;
The problem with this query was that the default numeric formatting didn't
look good, even after applying the ROUND function. Scott can apply another func-
tion here, TO_CHAR, to force the bonus to have two decimal places, even if the
bonus does not have any significance beyond the first decimal point. The TO_
CHAR function specifies the value to be formatted and the desired format, and it
can be used to format both numbers and date values. Here, Scott wants to fix
that rounded number:
select ename, sal, to_char(round(sqrt(sal),2),'9999.99')
"Bonus" from emp;
