Re: interesting date related question

Andy,
  when you work with date datatype you have   to keep in mind that it contains a time as well.

  When you insert your row with SYSDATE it contains   a time eg. 10-MAY-1999 15:53.

  If you compare that with 10-MAY-1999 it is not     equal.

  And that is the reason why you have to truncate it.

  If you don't want to change your programs, the only   way I see is that you update all your dates to

     d1 = TRUNC(d1)

  and create a trigger on your table which changes the     value of d1 to TRUNC(d1).

  In my opinion changing the programs to use TRUNC is   the best solution with the less possibility of failure   (eg. due to disabled trigger).

Regards
Dante
In article <7h76mh$bcv$1_at_nnrp1.deja.com>,   andyho99_at_my-dejanews.com wrote:
> Hi,
>
> I have been using sysdate for programs need
> timestamps while inserting a row. I noticed
> when I select the rows by certain date, it
> won't work unless I use trunc.
>
> For example:
>
> -- create test table
> create table aho_test
> (c1 char(4) not null,
> n1 number not null,
> d1 date);
> *****************************************
> -- insert something to test table
> insert into aho_test (c1, n1, d1) values
> ('andy', 123, sysdate);
>
> -- select does not work
> select * from aho_test
> where d1 = to_date('10-MAY-99', 'DD-MON-YY')
> /
>
> -- results
> no rows selected
> ***************************************
>
> -- select does work
> select * from aho_test
> where trunc(d1) = to_date('10-MAY-99', 'DD-MON-YY')
> /
>
> -- results:
> C1 N1 D1
> ---- ---------- ---------
> ANDY 123 10-MAY-99
>
> *********************************************
> I wonder whether there is way to get correct result
> without adding "trunc" because there are quite a few programs
> need to be changed. Hopefully, there is a setting in Oracle
> we may change.
>
> Thanks, Andy
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---
>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Mon May 10 1999 - 14:16:48 CDT