using TRUNC in timestamps - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: using TRUNC in timestamps

  1. #11
    Join Date
    Jan 2004
    Posts
    162
    ...in which case wouldn't you just do...
    Code:
    l_delta_all := f_time_in_out + f_margin_in_out_after - (f_txn_in_out - TRUNC (f_txn_in_out));

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

    TRUNC and 10g

    While extract does the job very nicely, trunc was altered in 10g to work with timestamp as will as date.

  3. #13
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41
    Sir, See this...

    ************************************************************
    SQL> select cast(systimestamp as time) from dual;

    CAST(SYSTIMESTAMPASTIME)
    ---------------------------------------------------------------------------
    10.41.48 AM
    ************************************************************

    It could solve the problem.

  4. #14
    Join Date
    Jan 2004
    Posts
    162
    Why on earth are you using 9.2.0.1? With all due respect you should be patching your databases. This works fine in 9.2.0.6.
    Code:
    Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
    JServer Release 9.2.0.6.0 - Production
    
    SQL> SELECT CURRENT_TIMESTAMP - TRUNC (CURRENT_TIMESTAMP)
      2  FROM   dual;
    
    CURRENT_TIMESTAMP-TRUNC(CURRENT_TIMESTAMP)
    ---------------------------------------------------------------------------
    +000000000 09:48:51.709693
    
    SQL>
    Good suggestion regarding CAST but I don't think that is going to work in PL/SQL.

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

    Exclamation

    Yea,,,,,
    I doesn't work with PL/SQL?????? Why...?

    *****************************************************************
    SQL> create or replace FUNCTION test101 RETURN timestamp IS
    2 v timestamp;
    3 BEGIN
    4 select cast(systimestamp as time) into v from dual;
    5 END;
    6 /

    Warning: Function created with compilation errors.

    SQL> show err
    Errors for FUNCTION TEST101:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    4/3 PL/SQL: SQL Statement ignored
    4/10 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got
    TIME

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

    It's paradox...

  6. #16
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41
    Sorry sir, but do you have any idea about how to get this patch (9.2.0.6)... in Oracle Site there is no more (9.2.0.2) for Windows...

    http://www.oracle.com/technology/sof...e9i/index.html

    Thank you again.

  7. #17
    Join Date
    Jan 2004
    Posts
    162
    Metalink is where the patches are at.

    I find it rather annoying that you would post verbatim quotes here from people on other forums (and vice versa) with no credit implying that it were somehow your own work.

  8. #18
    Join Date
    Jan 2004
    Posts
    162
    I think if you read Technet Forums thread again you will see that Laurent provided workaround for versions prior to 9.2.0.5.
    Code:
    CURRENT_TIMESTAMP - TRUNC (CAST (CURRENT_TIMESTAMP AS DATE))

  9. #19
    Join Date
    Sep 2003
    Location
    Detroit, MI
    Posts
    41
    Yea, See this...
    ********************************************************************
    SQL> select CURRENT_TIMESTAMP - TRUNC (CAST (CURRENT_TIMESTAMP AS DATE)) from dual;

    CURRENT_TIMESTAMP-TRUNC(CAST(CURRENT_TIMESTAMPASDATE))
    ---------------------------------------------------------------------------
    +000000000 14:18:31.879000

    SQL> create or replace FUNCTION test101 RETURN timestamp IS
    2 v timestamp;
    3 BEGIN
    4 select CURRENT_TIMESTAMP - TRUNC (CAST (CURRENT_TIMESTAMP AS DATE)) into v from dual;
    5 end;
    6 /

    Warning: Function created with compilation errors.

    SQL> show err
    Errors for FUNCTION TEST101:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    4/1 PL/SQL: SQL Statement ignored
    4/26 PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got
    INTERVAL

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

    The same, work with SQL without PL/SQL.

  10. #20
    Join Date
    Jan 2004
    Posts
    162
    Yeah? So RTFM.

    The result of TIMESTAMP - TIMESTAMP is INTERVAL DAY TO SECOND, not TIMESTAMP, as documented by Oracle here...

    http://download-west.oracle.com/docs...01.htm#g196492

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