Oracle SQL Between Dates Query

Question

I don't understand why my query is not returning anything: ``` select * from emp where hiredate between to_date ('01/01/81', 'mm/dd/yy') and to_date ('12/31/81', 'mm/dd/yy'); ``` Here is the structure of the table: ``` CREATE TABLE EMP ( EMPNO NUMBER(4) NOT NULL, ENAME CHAR(10), JOB CHAR(9), MGR NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) NOT NULL, CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO), CONSTRAINT EMP_PRIM_KEY PRIMARY KEY (EMPNO)); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-1981',5000,NULL,10); INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-1981',2850,NULL,30); INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-1981',2450,NULL,10); INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-1981',2975,NULL,20); INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-1981',1250,1400,30); INSERT INTO EMP VALUES (7499,'ALLEN','SALE…

Accepted Answer

Another difficulty with the statement in the OP

```
select * from emp where hiredate between to_date ('01/01/81',
 'mm/dd/yy') and to_date ('12/31/81', 'mm/dd/yy');
```

is that `hiredate` could contain a time part. Given the sample data, it doesn't, but there's nothing preventing it from happening. With that in mind, a better approach -- better than using `BETWEEN` -- would be the following:

```
SELECT * FROM emp
 WHERE hiredate >= DATE'1981-01-01'
   AND hiredate < DATE'1982-01-01';
```

One could also use `TRUNC()`:

```
SELECT * FROM emp
 WHERE TRUNC(hiredate, 'YEAR') = DATE'1981-01-01';
```

I can't see any reason to use `TO_DATE()` now that Oracle supports ANSI date literals (since 9i).

Oracle SQL Between Dates Query

Solution

Problem