Re: Date Comparison

Steve Dirschel (steve_dirschel_at_cargill.com) writes:
> 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.

  All date type columns implicitely contain time stamp. The time when   records are inserted and the implicit time in your specific date could   not be the same. To remedy this, there are 2 solutions:

1. TRUNC the sysdate when inserting records. e.g.: INSERT INTO dummy VALUES (TRUNC(SYSDATE)) OR
2. SELECT * FROM dummy WHERE TO_CHAR(a,'DD-MON-YY') = '10-DEC-96'.

  Hope this helps. Received on Fri Dec 13 1996 - 00:00:00 CST