文件名称:使用Date函数-SQL语言基础
文件大小:5.26MB
文件格式:PPT
更新时间:2024-05-15 13:24:54
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.