Oracle - Converting Date value TO_CHAR()

2
votes

Ok, so I want to convert a date value to properly format my date for comparison, however, will converting my "DATE" data type to char affect indexing when comparing on that field? Can I resolve this by doing to_date(tochar())? Any advice on this would be greatly appreciated Thanks!

EDIT - Sorry for the lack of specifics.... basically I need to eliminate the time stamp from my date, I used the following and it appears to work TO_DATE(TO_CHAR, 'YYYY-MM-DD'),'YYYY-MM-DD'), mind you I don't know if this is good practice or not, but at least (or so I think) now it's comparing a DATE against a DATE, and not a string.

3
oracleindexingoracle10goracle11g

3 Answers

8
votes

If you are doing a comparison, you should not be converting the date to a string. You should be comparing to another date. Otherwise, Oracle won't be able to use a non function-based index on the date column.

In general, that is, you're much better off coding

WHERE some_indexed_date_column = to_date( :string_bind_variable, 
                                          <<format mask>> )

rather than

WHERE to_char( some_indexed_date_column, 
               <<format mask>> ) = :string_bind_variable

Of course, if your bind variable can be a DATE rather than a VARCHAR2, that's even better because then you don't have to do any data type conversion and the optimizer has a much easier time of estimating cardinalities.

If you are trying to do some manipulation of the date-- for example, if you want to compare the day portion while omitting the time portion of the date-- you may want to use function-based indexes. For example, if you wanted to find all the rows that were created some time today

WHERE trunc( some_date_column ) = date '2011-11-04'

you could either create a function-based index on the date column

CREATE INDEX idx_trunc_dt
    ON table_name( trunc( some_date_column ) )

or you could rewrite the query to do something like

WHERE some_date_column >= date '2011-11-04'
  AND some_date_column <  date '2011-11-05'
3
votes

You should compare dates as dates, not as strings. If comparing a date to a string, convert the string to a date to compare.

2
votes

IMO, you should convert your comparison value to the format stored in the database. Otherwise, you will need to create a function based index on the DATE column to take advantage of indexing. So, if you have an input character date of, say, 11/4/2011, you could compare it in a where clause thusly:

SELECT ...
  FROM your_table
 WHERE the_date_column = TO_DATE('11/4/2011','MM/DD/YYYY');