-
...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));
-
TRUNC and 10g
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.
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|