Why does my SQL query of date fields sometimes need trunc() (Oracle)?

Question

I have two tables where I was querying on a date column in each table.

In one case need to use the trunc() function on the date field to get values back, on the other I do not.

That is this works on table 1:

SELECT to_char( datecol1 ,'mm/dd/yyyy hh:mm:ss') 
FROM table1 where datecol1    =to_date('10/07/2010', 'mm/dd/yyyy');

But on table 2 the above syntax did not work and I needed the trunc(), such as:

SELECT to_char( datecol2 ,'mm/dd/yyyy hh:mm:ss') 
FROM table2 where trunc(datecol2) =to_date('10/07/2010', 'mm/dd/yyyy');

Three things to note:

1. in querying table1 with to_char(datecol1 ,'mm/dd/yyyy hh:mm:ss') it looks as if all the times are between 12:00 and 12:10, but values were inserted throughout the day
2. when inserting records into table1 I just insert mm/dd/yyyy, no time
3. when inserting records into table2 I inserted with the time

So can someone explain:

• why the truncate is not needed on table1 but on table2?
• why all values in table1 are between 12:00 and 12:10?

Answer 1

In table1 you have no 'time-of-day' component to the data, so a date should match - which is what you observed. But, you used mm for the formatting of the minute part of the time - but mm is month, not minute (mi). This is why you see times other than 12:00, and why they only range up to around 12:10 (you only have data for this year perhaps?)

In table2, as you have a 'time-of-day' component to the data, you need to truncate that away in order to match a date-only value, which is what the to_date() function returns, given the format you have used.

Answer 2

*when inserting records into table1 I just insert mm/dd/yyyy, no time

when inserting records into table2 I inserted with the time*

And that is why you need trunc on the second query. to_date('10/07/2010', 'mm/dd/yyyy') is generating a "date" with a time component of midnight, just like the inserts into Table1 without a time component. In the second case there is a time component, trunc strips that off so you can just compare the date portion the date portion of to_date('10/07/2010', 'mm/dd/yyyy')

If dateCol1 has an index on it you may want to rewrite your second query as:

SELECT to_char( datecol2 ,'mm/dd/yyyy hh:mi:ss')  
FROM table2 
WHERE to_date('10/07/2010', 'mm/dd/yyyy') <= dateCol2
and dateCol2 < to_date('10/08/2010', 'mm/dd/yyyy')

or

SELECT to_char( datecol2 ,'mm/dd/yyyy hh:mi:ss')  
FROM table2 
WHERE dateCol2 between to_date('10/07/2010', 'mm/dd/yyyy') 
    and to_date('10/07/2010 11:59:59 PM', 'MM/DD/YYYY HH12:Mi:SS AM')

Or create a function based index on trunc(dateCol2) instead of normal index on dateCol2, depending on other queries.

Because columns that participate in functions generally can't use indexes, and will force a table scan, taking options away from the optimizer.