Oracle date comparison using to_date not working

2
votes

I am using Oracle XE on my machine.
Defined a table as : 

Name       Type
ENAME      VARCHAR2(20) 
DOJ        DATE

Firing a simple select:

select * from test1.tasty1;

ENAME                DOJ
sat                  08-DEC-16

So ok - I am aware that DATE field has time component in it.
The following query using TRUNC works fine:

select * from test1.tasty1 where trunc(DOJ) = '08-DEC-16';

Now I wanted to test the 'to_date' function - but none of the below queries worked - wonder why ?

select * from test1.tasty1 where DOJ = to_date('08-12-2016','DD-MM-YYYY');
select * from test1.tasty1 where DOJ = to_date('08-DEC-2016','DD-MON-YYYY');
select * from test1.tasty1 where DOJ = to_date('08-DEC-16','DD-MON-YY');
select * from test1.tasty1 where DOJ = to_date('08-DEC-16','dd-mon-RR');

Had taken a look at the following on SO:
Oracle TO_DATE not working

so not sure what is wrong here ?

2
oracledate
Please try the following query: SELECT * FROM test1.tasty1 WHERE DOJ = '2016-08-12' ... does this return anything? AFAIK some of the 4 queries you tried should be returning that record. – Tim Biegeleisen
the above fails with 'literal does not match format string' if I try 'select * from test1.tasty1 where DOJ = '08-DEC-16';' gives no results – satish marathe
This is not what I asked you to run. I asked you to run WHERE DOJ = '2016-08-12' – Tim Biegeleisen
well if you read my response- I did respond that it fails with 'literal does not match format string' – satish marathe
Are you certain that DOJ is a date column? This is very strange behavior I am seeing here. – Tim Biegeleisen

2 Answers

2
votes

From your question and comments, it appears that this is the sequence of events which happened.

You did the following INSERT into your table:

INSERT INTO test1.tasty1 VALUES ('sat', SYSDATE)

Keep in mind that dates in Oracle have both a date and a time component. So even though you did insert the date '2016-12-08' you also inserted a time component. As a result, the following query is not returning any records:

SELECT * FROM test1.tasty1 WHERE DOJ = '2016-08-12'

This is probably because you never specified the time component, and therefore the record you inserted earlier is not matching. If you want to compare only the date portion, you can use TRUNC as follows:

SELECT * FROM test1.tasty1 WHERE TRUNC(DOJ) = '2016-08-12'

The solution to your problem moving forward would be to wrap SYSDATE with TRUNC during the insert, if you really only want to deal with the date components.

By the way, the format '08-DEC-16' used as a literal will not be recognized by Oracle as a valid date. Instead, use '2016-12-08'.

2
votes

Have you tried like this as comparison of date with date is correct:

select * from test1.tasty1 where to_date(DOJ,'DD-MM-YYYY') = to_date('08-12-2016','DD-MM-YYYY');

Compare apples with apples and not with mangoes.