Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 8.0.5 DATE Type
A copy of this was sent to Greg Akins <insomnia_at_a1usa.net>
(if that email address didn't require changing)
On Tue, 08 Jun 1999 05:30:53 -0800, you wrote:
>I am trying to update a table. The attribute is CLOSE_DT
>defined as type DATE.
>
>There are two distinct values '06/08/99 6:59' and '01/01/01
>6:59' (for example). I want to truncate the dates to
>remove the time component.
>
>Update TABLE1 set close_dt = trunc(close_dt) ;
>
>Now I have two values '01/01/01 12:00AM' & '06/08/99
>7:00AM'.
>
>Notice the time gets updated to current time, but not
>truncated.
>
>Any ideas?
Any triggers out there lurking around that are defaulting the close_dt column to SYSDATE? I just ran:
SQL> create table table1 ( close_dt date );
Table created.
SQL>
SQL> insert into table1 values ( sysdate );
1 row created.
SQL> insert into table1 values ( to_date( '01/01/01 6:59', 'dd/mm/yy hh:mi' ) );
1 row created.
SQL>
SQL> select to_char( close_dt, 'dd-mon-yyyy hh24:mi:ss' ) from table1;
TO_CHAR(CLOSE_DT,'DD
SQL> update table1 set close_dt = trunc(close_dt);
2 rows updated.
SQL> select to_char( close_dt, 'dd-mon-yyyy hh24:mi:ss' ) from table1;
TO_CHAR(CLOSE_DT,'DD
it works as it is supposed to....
>
>-greg -> insomnia_at_a1usa.net
>
>
>
>**** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--