SQL query using oracle 10g database datetime equal not working

Question

Hi,

I have SQL query like this.

select * 
from TABLE_A
where LogDateTime >= startdatetime and LogDateTime <= enddatetime;

But some reason enddatetime equal is not working. I have the record with the date 11/23/09 8:50:09. When I select enddatetime as 11/23/09 8:50:09 it's not returning this record. It's returning till 8.49:59. What could be the problem? Why the timestamp is not working? Please let me know.

Thank you..

Answer 1

What datatype is enddatetime? If it's a timestamp then there might be a mismatch between the type of the variable you are passing in (DateTime) and the type of the data in the table (Timestamp) this could cause this as there might not be a timestamp valeue that exactly matches the datetime value ... and the closest available value might be "off" in the direction that causes the record to be filtered out.

Answer 2

Is LogDateTime of TIMESTAMP datatype? It stores fractional part of seconds. Possibly your date is not exactly 11/23/09 8:50:09.
Try to output your date using TO_CHAR(LogDateTime,'MM/DD/YYYY HH24:MI:SS.FF') to see if that's the case.

Answer 3

Oracle might store the datetime in higher precision, like 8:49:59.200. That's bigger than 8:49:59, but it will display the same.

Try this WHERE clause:

LogDateTime < (enddatetime + INTERVAL '1' SECOND)

This will still include anything which has the same starting second as the enddatetime.