...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));
Printable View
...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));
While extract does the job very nicely, trunc was altered in 10g to work with timestamp as will as date.
Sir, See this...
************************************************************
SQL> select cast(systimestamp as time) from dual;
CAST(SYSTIMESTAMPASTIME)
---------------------------------------------------------------------------
10.41.48 AM
************************************************************
It could solve the problem.
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.
Good suggestion regarding CAST but I don't think that is going to work in PL/SQL.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>
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...
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.
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.
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))
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.
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