Re: Date Comparison

Scott Mattes wrote:
>
> I know this one (I think)!
>
> I ran into this also when I started working with Oracle. This happens
> because a date field is really a date and the time (and, to complicate
> matters, the default display format for a date field is just the date,
> so you don't see the date/time). If you store just a date what is
> actually stored is the date and 12:00. In your example you are storing
> the current date AND time! You can see this by redefining the output
> display of this field to show date and time, or you can use a function
> to compare just the date part of the table field (don't have the books
> handy, sorry), or you could change your query to include the 12:00 (or
> is it 12:00:00).
>
> Steve Dirschel <steve_dirschel_at_cargill.com> wrote:
>
> >Could someone please explain this to me:
 

> >Create table dummy (a date);
 

> >Insert into dummy values (sysdate);
> >Insert into dummy values (sysdate);
> >Commit;
 

> >Select * from dummy;
> >10-DEC-96
> >10-DEC-96
 
> >Select * from dummy where a = '10-DEC-96'
 

> >0 rows selected
 

> >Select * from dummy where a > '10-DEC-96'
> >10-DEC-96
> >10-DEC-96
> >2 rows selected
 

> >Why do I not get 2 rows returned when I try the select * from dummy
> >where a = '10-DEC-96' ? I realize I can do a "less than 11-DEC-96 and
> >greater than 10-DEC-96" but it seems like I shouldn't have to do all
> >of that typing.
 

> >Thanks in advance
> The reason is because there is a time stamp to the date. In order to see
those, you will need to to_char(a, <format>) where format is the format that you want to see the date and time.
Have fun!

Keith Received on Thu Dec 19 1996 - 00:00:00 CST