problem in to_date function

[KULJEET]

hi
i have a table c_table with two columns
no,
date
it contains records

no n_date
1 10-AUG-03
2 21-AUG-03

when we issue
select * from c_table where n_date='10-AUG-03';
it will successfully return the record

but when we give
select * from c_table where n_date='21-AUG-03';

it will not return the record

after using to_date function like
select * from c_table where to_date(n_date, 'DD/MM/YY')='21/08/03';

it will successfully return the records
but the main problem is that
when we have table that contains 50,000,00 records
**and then using to_date function then it will slow down the performance

how we can solve the.

[Nic]

Re: problem in to_date function

Hi,
for part of your question related to the performance, i think you could modify your query to get

select * from c_table where n_date = to_date('21/08/03','DD/MM/YY');

so the to_date function get call only once, of course if you have store the date data with differents time you still will need to
call the trunc function.

select * from c_table where trunc(n_date) = to_date('21/08/03','DD/MM/YY');

"KULJEET" <kuljeet_twtpl@ hotmail.comwrot e in message news:febbed51.0 308222045.3f3b3 845@posting.goo gle.com...

hi
i have a table c_table with two columns
no,
date
it contains records
>
no n_date
1 10-AUG-03
2 21-AUG-03
>
when we issue
select * from c_table where n_date='10-AUG-03';
it will successfully return the record
>
but when we give
select * from c_table where n_date='21-AUG-03';
>
it will not return the record
>
after using to_date function like
select * from c_table where to_date(n_date, 'DD/MM/YY')='21/08/03';
>
it will successfully return the records
but the main problem is that
when we have table that contains 50,000,00 records
**and then using to_date function then it will slow down the performance
>
how we can solve the.

[Ed prochak]

Re: problem in to_date function

kuljeet_twtpl@h otmail.com (KULJEET) wrote in message news:<febbed51. 0308252055.388c 404a@posting.go ogle.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