Re: problem in to_date function

kuljeet_twtpl_at_hotmail.com (KULJEET) wrote in message news:<febbed51.0308252055.388c404a_at_posting.google.com>... []
>
> the main problem is that in
> when i issue
> ---------------------
> select to TO_CHAR(N_DATE,'DD/MM/YY HH24:MI') from c_table
> the it will return
> 10/08/03 00:00
> 10/08/03 00:00
> 10/08/03 00:00
> 21/08/03 16:21
> ----------------
> so
> 10/08/03 date it will store time as 00:00
> 10/08/03 00:00
>
> but in 21/08/03 it will store time as 16:21
>
> 21/08/03 16:21
>
>
> BUT IT WILL NOT WORK
> select * from c_table where n_date = to_date('21/08/03','DD/MM/YY');
> BUT THIS WILL WORK
> select * from c_table where trunc(C_date) = to_date('21/08/03','DD/MM/YY');
> CAN TRUNC FUNCTION SLOW DOWN THE PERFORMANCE????
Unless you have created a matching function-based index, any function on a column will prevent the use of indices and thus Possibly affect performance.

The questions you need to ask yourself are: A:Is the time portion of the date really needed? B:Is the performance unacceptable using the various function based solutions?

If the answer to A is NO, then update the data to truncate (or round) the time portion away, then all your dates will be at midnight and will match appropriately and use possible indices as the Optimizer sees fit.

If the answer to A is YES and the answer to B is NO, then using the trunc() doesn't hurt you case.

If A is YES and B is YES, then consider either: creating a function based index OR adding a search date column (another column that contains the truncated date values used only for searching and indexing).

finally here's an untested query that should still use possible indices and gets by the trunc():

   SELECT * FROM c_table WHERE n_date BETWEEN    to_date('21/08/03','DD/MM/YY') AND to_date('21/08/03','DD/MM/YY')+1 ;

(NOTE the plus one).

   HTH Received on Tue Aug 26 2003 - 20:02:04 CEST