我想在“20-FEB-81”之间选择hiredate以及“81 年 5 月 1 日”

Question

我想要“20-FEB-81”和“01-MAY-81”之间的员工姓名、工作、雇佣日期，并按升序

查询运行，但出现错误

SQL> select ename, job, hiredate where hiredate between '20-FEB-81' AND '01-MAY-81'   from emp;
select ename, job, hiredate where hiredate between '20-FEB-81' AND '01-MAY-81' from emp
                        *
 ERROR at line 1:
 ORA-00923: FROM keyword not found where expected


 SQL>

我的表 SQL>从 emp 中选择 empno、ename、job、hiredate、sal；

 EMPNO ENAME      JOB       HIREDATE         SAL
 ---------- ---------- --------- --------- ----------
  7839 KING       PRESIDENT 17-NOV-81       5000
  7698 BLAKE      MANAGER   01-MAY-81       2850
  7782 CLARK      MANAGER   09-JUN-81       2450
  7566 JONES      MANAGER   02-APR-81       2975
  7654 MARTIN     SALESMAN  28-SEP-81       1250
  7499 ALLEN      SALESMAN  20-FEB-81       1600
  7844 TURNER     SALESMAN  08-SEP-81       1500
  7900 JAMES      CLERK     03-DEC-81        950
  7521 WARD       SALESMAN  22-FEB-81       1250
  7902 FORD       ANALYST   03-DEC-81       3000
  7369 SMITH      CLERK     17-DEC-80        800

 EMPNO ENAME      JOB       HIREDATE         SAL
 ---------- ---------- --------- --------- ----------
  7788 SCOTT      ANALYST   09-DEC-82       3000
  7876 ADAMS      CLERK     12-JAN-83       1100
  7934 MILLER     CLERK     23-JAN-82       1300

 14 rows selected.

 SQL>

Answer 1

WHERE 部分位于 FROM 部分之后。

select ename, job, hiredate 
from emp
where hiredate between '20-FEB-81' AND '01-MAY-81'

请注意，如果 NLS 设置发生更改，您的日期文字可能并不总是有效。强烈建议使用 to_date() 代替。

select ename, job, hiredate 
from emp
where hiredate between to_date('20-FEB-81', 'DD-MON-RR') AND to_date('01-MAY-81', 'DD-MON-RR')

但这仍然受到语言设置问题的影响，最好根本不要使用月份名称：

select ename, job, hiredate 
from emp
where hiredate between to_date('20-02-81', 'DD-MM-RR') AND to_date('01-05-81', 'DD-MM-RR')

Answer 2

mysql> select * from emp where year(hiredate)>=1981 and year(hiredate)<=1983;

输出：

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

13 rows in set (0.00 sec)