Re: dates in where clause using to_date and =

Pete,

It is not date formatting. Oracle date is actually a timestamp with the precision of seconds. If dont specify hours, or minutes, or seconds, Oracle
supplies missing data and converts all into a timestamp. That is if you insert several SYSDATEs into your table one every second, all your values will be different by one second. Thus

    eventdale = to_date('07/06/2000','MM/DD/YYYY') can be FALSE because of those seconds, but BETWEEN will work allright.

Usually they use TRUNC/ROUND functions to truncate timestamps before doing comparisons.

Regards
Karen

Peter Shankey wrote:

> 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