Querying datetime in oracle

Question

I have a date type column in a table, where I store date along with time.

I want to query it by WHERE clause I did it this way:

select * 
from conference_hall_book 
where to_date(end_time,'dd/mon/yyyy hh24:mi:ss') <= to_date('26/oct/2013 15:00:00','dd/mon/yyyy hh24:mi:ss')

But the result has 27/10/2013 8:00:00 AM also in end_time column.

Can any one help me finding the mistake?

Answer 1

The problem occurs because of

to_date(end_time,'dd/mon/yyyy hh24:mi:ss')

This is a wrong usage of the to_date function. To_date converts a string to a date. When Oracle sees this expression, it will automatically convert the end_time value to a string, using the configured date format of your database/session. This format typically doesn't include the time part, so a date with the value of "27/10/2013 8:00:00 AM" will be converted to the string "27/10/2013" (if your database date format is dd/mm/yyyy). Your to_date expression will then convert the string value "27/10/2013" back to a date. The resulting date value will be "27/10/2013 00:00:00", so you will have lost the time portion of your original date.

The simple and correct solution is to drop the to_date(end_time) expression and just use end_time. This will also ensure that if you have index on end_time, the query will be able to use that index.

select * 
from conference_hall_book 
where end_time <= to_date('26/oct/2013 15:00:00','dd/mon/yyyy hh24:mi:ss')