Converting Date value TO
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.
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'
You should compare dates as dates, not as strings. If comparing a date to a string, convert the string to a date to compare.
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');
链接地址: http://www.djcxy.com/p/6294.html
上一篇: 02089与Java
下一篇: 将日期值转换为