【文件属性】：
文件名称：使用Date函数-SQL语言基础
文件大小：5.26MB
文件格式：PPT
更新时间：2021-04-25 19:38:14
SQL 基础
使用Date函数
*
MONTHS_BETWEEN ('01-SEP-05','11-JAN-04')
ADD_MONTHS ('11-JAN-05',6)
NEXT_DAY ('01-SEP-05','FRIDAY')
LAST_DAY('01-SEP-05')
19.6774194
'11-JUL-05'
'02-SEP-05'
'30-SEP-05'
*
SELECT MONTHS_BETWEEN ((to_date('01-SEP-05','dd-mm-yy')),(to_date('11-JAN-04','dd-mm-yy'))) from dual;
select add_months ((to_date('01-JAN-05','dd-mm-yy')),6) from dual;
select next_day((TO_DATE('01-SEP-05','DD-MM-YY')),'FRIDAY') from dual;
SELECT last_day ((to_date('01-SEP-05','dd-mm-yy'))) from dual;
Date Functions (continued)
For all employees employed for fewer than 200 months, display the employee number, hire date, number of months employed, six-month review date, first Friday after hire date, and last day of the month when hired.
SQL> SELECT empno, hiredate,
2 MONTHS_BETWEEN(SYSDATE, hiredate) TENURE,
3 ADD_MONTHS(hiredate, 6) REVIEW,
4 NEXT_DAY(hiredate, 'FRIDAY'), LAST_DAY(hiredate)
5 FROM emp
6 WHERE MONTHS_BETWEEN (SYSDATE, hiredate)<200;
EMPNO HIREDATE TENURE REVIEW NEXT_DAY( LAST_DAY( ------ --------- --------- --------- --------- ---------
7839 17-NOV-81 192.24794 17-MAY-82 20-NOV-81 30-NOV-81
7698 01-MAY-81 198.76407 01-NOV-81 08-MAY-81 31-MAY-81
...
11 rows selected.