ku***********@hotmail.com (KULJEET) wrote in message news:<fe**************************@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