using TRUNC in timestamps
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: using TRUNC in timestamps

Hybrid View

  1. #1
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41

    Thumbs up using TRUNC in timestamps

    Sir,
    As you know we were using TRUNC function in DATE datatype. But I want to use it with TIMESTAMP datatypes. For example:
    *************************************************************
    select trunc(systimestamp,'hh24:mi:ss') from dual;
    *************************************************************

    The main idea to get some part of given timestamp without using type conversions (i.e. TO_CHAR).

    Note that this is useful when you want to ADD or Subtract the time side from timestamp with interval values and comparing them to another timestamps fields or values.

    Thank you in advance.

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    TRUNC works for TIMESTAMP in a similar way to the way it works for DATEs (what you have posted is not supported for either)..

    Perhaps you are looking for something like...
    Code:
    SELECT EXTRACT (HOUR FROM current_timestamp)
    FROM   dual;
    ...but that is still a type conversion.

  3. #3
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41
    Sir,
    When using EXTRACT you can just get one type at a time -in your example you got only the Hour side-. But I need get the time part (i.e. HH24:MI:SS) which is applicable on DATE datatype by using TRUNC.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Lowaiy
    ..... which is applicable on DATE datatype by using TRUNC.
    No, it is not (as padders allready said). You can get time portion of the date unly by using TO_CHAR(), you can't get it with TRUNC(). And this behavior is the same for DATE and TIMESTAMP datatype (also, as padders allready told you).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2004
    Posts
    162
    Still trying to understand what you want here.

    If you want an INTERVAL DAY TO SECOND representing the time from a given TIMESTAMP you could...
    Code:
    SELECT current_timestamp - TRUNC (current_timestamp)
    FROM   dual;

  6. #6
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41
    Sorry sir, you're right...
    But I still expect function could do this without converting it to char datatype. It's important -as I said- when you want to add an interval value and Add/Subtract another time value and make some comparisons. Definitely you don't like something like this:
    *****************************************************************
    L_Delta_All:=To_Timestamp(to_char(F_TIME_IN_OUT+F_MARGIN_IN_OUT_AFTER,'hh24:mi:ss'),'hh24:mi:ss')-
    To_Timestamp(to_char(F_TXN_IN_OUT,'hh24:mi:ss'),'hh24:mi:ss');
    *****************************************************************

    The previous statement is simple Add Time portion to Interval and subtracts the time portion from another field.
    ********************************************************************
    L_Delta_All:=(F_TIME_IN_OUT + F_MARGIN_IN_OUT_AFTER) - F_TXN_IN_OUT;
    ********************************************************************

    So there's anyway in order to make the previous statement in simplest way.

    Thanks.

  7. #7
    Join Date
    Jan 2004
    Posts
    162
    Presumably you expect us to guess the datatypes of F_TIME_IN_OUT, F_MARGIN_IN_OUT_AFTER and F_TXN_IN_OUT?

  8. #8
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41
    F_TIME_IN_OUT and F_TXN_IN_OUT is timestamp
    F_MARGIN_IN_OUT_AFTER is INTERVAL DAY TO SECOND

    And the previous code shows error:
    ********************************************************************
    SQL> SELECT current_timestamp - TRUNC (current_timestamp)
    2 FROM dual;
    SELECT current_timestamp - TRUNC (current_timestamp)
    *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP
    ********************************************************************

  9. #9
    Join Date
    Jan 2004
    Posts
    162
    Hmmmm. Works fine for me.
    Code:
    Personal Oracle Database 10g Release 10.1.0.2.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> SELECT current_timestamp - TRUNC (current_timestamp) time 
      2  FROM   dual;
    
    TIME
    -----------------------------------------------------------------------
    +000000000 14:53:04.941000
    
    SQL>

  10. #10
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41
    So What... Oracle DB Bug...? Huh... Or new feature...

    ********************************************************************
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production

    SQL> SELECT current_timestamp - TRUNC (current_timestamp) time
    2 FROM dual
    3 /
    SELECT current_timestamp - TRUNC (current_timestamp) time
    *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP


    SQL>
    **********************************************************************

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