Re: date data type and = operator

Use to_date to make sure you are getting the correct century/time. When you specify "22-NOV-98", it will assume some century (we hope 1900s) and midnight. The following should take care of these problems:

select *
from table1
where trunc(selldate) = to_date('22-NOV-1998', 'DD-MON-YYYY');

(substitute for 19 whatever century you are interested in) You can see what those dates really are by using to_char:

select to_char(selldate, 'DD-MON-YYYY HH24:MI:SS') from table1

Rob Williamson wrote in message <3648D483.85CDB083_at_physics.umd.edu>...
>I have a date field in Oracle using the date type and the out put
>of a select statement gives the date in dd-MMM-yy but if
>I use the date field in where clause to see an individual date
>it gives no error but returns no rows ( I know the date is there)
>I assume this has something to do with the time but cannot figure it out
>
>my statement looks like this
>
>select *
>from table1
>where selldate = '22-NOV-98';
>
>this returns no rows and no error
>
>yet the select * from table1; returns many rows with the
>date 22-NOV-98 in the same format.
>
>If I change the = to > it is OK so I assume the time is in the actual
>table.
>
>How can I see what is in the actual table?
>
>And what would be the format for the = ' date /time '?
>
>I realize this is probably elementary but I can't find it
>in any the books I have.
>
>Thanks in advance
>
>
Received on Tue Nov 10 1998 - 00:00:00 CST