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','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.

I think I know now why it is not returning any result -- it is because the year is vague, it doesn't know which year to return or it uses the current century that is why it is not giving any result. Just a thought. — ddoctor
– ddoctor, Commented Feb 22, 2015 at 1:14
@ddoctor you can post this as an answer. It's will surely get upvoted. — Mureinik
– Mureinik, Commented Feb 22, 2015 at 7:25

Community · Accepted Answer · 2017-05-23 11:56:51Z

4

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'

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Feb 22, 2015 at 10:23

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wernfried Domscheit Over a year ago

Just for completeness, (non-preferred) TO approach would be to_date ('01/01/1981', 'mm/dd/yyyy') and to_date ('12/31/1981', 'mm/dd/yyyy') or (even worse) to_date ('01/01/81', 'mm/dd/rr') and to_date ('12/31/81', 'mm/dd/rr'). However, both of them would work out.

David Faber · Accepted Answer · 2015-02-23 00:43:52Z

3

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

answered Feb 23, 2015 at 0:43

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

2 Comments

ddoctor Over a year ago

Thank you very much for all the inputs.

Lukas Eder Over a year ago

The TRUNC() function will prevent index usage, unless there's a function-based index on TRUNC(hiredate, 'YEAR')...

Collectives™ on Stack Overflow

Oracle SQL Between Dates Query

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related