Removing trunc
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Removing trunc

  1. #1
    Join Date
    Apr 2001
    Posts
    112

    Removing trunc

    How do I get rid of the trunc on an equal predicate eg:
    where trunc(Hiredate)=TO_DATE('05/05/2005','MM/DD/YYYY') so that the index on Hiredate gets used ?

    Thanks

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    Does this work :

    Code:
    SQL> create table dt (d date);
    
    Table created.
    
    SQL> insert into dt values (sysdate);
    
    1 row created.
    
    SQL> insert into dt values (sysdate);
    
    1 row created.
    
    SQL> insert into dt values (sysdate);
    
    1 row created.
    
    SQL> insert into dt values (sysdate);
    
    1 row created.
    
    SQL> update dt set d=d-rownum;
    
    4 rows updated.
    
    SQL> select * from dt;
    
    D
    ---------
    06-JUN-05
    05-JUN-05
    04-JUN-05
    03-JUN-05
    
    SQL> select to_char(d,'DD-MON-YYYY HH24:MI:SS') from dt;
    
    TO_CHAR(D,'DD-MON-YY
    --------------------
    06-JUN-2005 16:09:19
    05-JUN-2005 16:09:22
    04-JUN-2005 16:09:24
    03-JUN-2005 16:09:25
    
    SQL> select * from dt where trunc(d)='04-JUN-05';
    
    D
    ---------
    04-JUN-05
    
    SQL> select * from dt where d > '04-JUN-05' and d < '05-JUN-05';
    
    D
    ---------
    04-JUN-05
    
    SQL>
    svk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width