trunc and timestamp
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: trunc and timestamp

  1. #1
    Join Date
    Jun 2000
    Posts
    295

    trunc and timestamp

    Hi,

    trunc can only apply on date, but not timestamp.
    I need portion of timestamp column, does anyone know how?

    For example:
    select sysdate, trunc(sysdate, 'month') from dual;
    returns:
    SYSDATE TRUNC(SYS
    --------- ---------
    23-AUG-04 01-AUG-04

    select current_timestamp from dual;
    returns:
    CURRENT_TIMESTAMP
    ---------------------------------------------------------------------------
    23-AUG-04 09.56.02.354243 PM -06:00

    I need somthing like:

    select trunc(current_timestamp, 'month') from dual;
    to give
    01-AUG-04 instead of error:
    *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP

    Oracle version 9.2.0.4.

    Thanks,

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Look for To_Char, To_Date functions
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You might also want to check the EXTRACT() function - it returns the desired field from datetime value.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    In 9i, use the CAST function:

    SQL> select trunc(cast (systimestamp as date),'month') from dual;

    TRUNC(CAS
    ---------
    01-AUG-04


    In 10g you can use trunc with timestamp:

    SQL> select trunc(systimestamp,'month') from dual;

    TRUNC(SYS
    ---------
    01-AUG-04

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