Re: dates in where clause using to_date and =

This is because of the time in your date field. When you do a select statement, especially with a format of MM/DD/YYYY you are seeing just the date and not the time. With your like statement or your between statement, it is taking care of the time. With the = statement it is assuming MM/DD/YYYY 00:00:00 and there is some time being stored. Try using a select statement that uses the format of MM/DD/YYYY hh:mi:ss and you will see the time. You can use the trunc() function on a date field so that it ignores the time.

Example
select eventdate
from countycal
where trunc(eventdate) = to_date('07/06/2000','MM/DD/YYYY')

John Jones
Senior Oracle DBA
Duke University OIT
john.jones_at_duke.edu
Peter Shankey <shankeyp_at_charlestoncounty.org> wrote in message news:398F6E93.BB7400DC_at_charlestoncounty.org...
> I would like to use the to_date function in a where clause.
> If I do
>
> select eventdate from countycal;
> stuff
> stuff
> 07/06/2000
> 07/06/2000
> 07/06/2000
> stuff
> stuff
> ie nls format is 'MM/DD/YYYY'
>
> however if I do:
>
> select eventdate
> from countycal
> where eventdate = to_date('07/06/2000','MM/DD/YYYY')
>
> no rows selected
>
> However if I do:
>
> select eventdate
> from countycal
> where eventdate like to_date('07/06/2000','MM/DD/YYYY')
> 07/06/2000
> 07/06/2000
> 07/06/2000
>
> or if I do
>
> select eventdate
> from countycal
> where eventdate BETWEEN to_date('07-05-2000','MM-DD-YYYY')
> and to_date('07-07-2000','MM-DD-YYYY')
> 07/06/2000
> 07/06/2000
> 07/06/2000
> 07/07/2000
>
> which make sense.
>
> I evidentally do not understand the use of the equals sign with repect
> to date
> formating. Could someone explain where I am missing it.
>
> Thanks
> Pete
>
Received on Mon Aug 07 2000 - 00:00:00 CDT