Why I can't compare dates?

Question

I have this simple query:

select sm.id, h.time
from main_data sm

INNER JOIN TA t on t.id = sm.id AND t.first=1
LEFT OUTER JOIN History h on h.id= sm.id

WHERE trunc(TO_DATE(h.time, 'DD-MM-YYYY')) BETWEEN trunc(TO_DATE('07.05.2015', 'DD.MM.YYYY')) AND trunc(TO_DATE('07.06.2015', 'DD.MM.YYYY'));

h.time looks like 07-MAY-15

The above query returns no results, even if there are dates between the specified parameters. BUT if I change the where clause to

WHERE trunc(TO_DATE(h.time, 'DD-MM-YYYY')) BETWEEN trunc(TO_DATE('07.05.15', 'DD-MM-YYYY')) AND trunc(TO_DATE('07.06.15', 'DD-MM-YYYY')) - the same dates but instead of 2015 there is just 15 I can see the results of the query.

The problem is that I'm expecting the user to enter a full year - not just 15. How can I avoid this mismatch?

Answer 1

First, your date comparison is too complicated. If h.time is an internal date format (which it should be), then just do:

WHERE h.time BETWEEN DATE '2015-05-07' AND DATE '2015-06-07'

Another very important issue with your query is that the WHERE clause is turning the LEFT JOIN into an INNER JOIN. Either change the join type to be consistent with the logic or move the WHERE to the ON clause (you will then get results even when there are no matching dates.