CODEWITHSUNDEEP
sqloracle-databasedate
ddoctor
ddoctor

Reputation: 103

Oracle SQL Between Dates Query

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','SALESMAN',7698,'20-FEB-1981',1600,300,30); 
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-1981',1500,0,30); 
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-1981',950,NULL,30); 
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-1981',1250,500,30); 
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-1981',3000,NULL,20); 
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-1980',800,NULL,20); 
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-1982',3000,NULL,20); 
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-1983',1100,NULL,20); 
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-1982',1300,NULL,10);

Please help me understand. Thank you.

Upvotes: 5

Views: 10426

Answers (2)

David Faber
David Faber

Reputation: 12486

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).

Upvotes: 3

Lukas Eder
Lukas Eder

Reputation: 220762

As already mentioned in the comments you would need to use the RR format as documented here, or probably better, use 4-digit year. Examples given by Wernfried in the comments:

  • to_date ('01/01/81', 'mm/dd/rr')
  • to_date ('01/01/1981', 'mm/dd/yyyy')

However, I strongly suggest you do not use Oracle's formatting function if you don't really need to do any formatting. Write this instead:

select * from emp 
where hiredate between date '1981-01-01' and date '1981-12-31';

The above is the SQL standard way of expressing date literals, also supported by Oracle. It is much easier to remember than the formatting strings, and it is independent of your locale / language, etc.

If you're trying to do the same with timestamps, there are also timestamp literals:

where hiredate between timestamp '1981-01-01 00:00:00.0'
                   and timestamp '1981-12-31 23:59:59.999'

Upvotes: 4

Related Questions