using TRUNC in timestamps

[padders]

...in which case wouldn't you just do...

l_delta_all := f_time_in_out + f_margin_in_out_after - (f_txn_in_out - TRUNC (f_txn_in_out));

[Lowaiy]

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

[Lowaiy]

Sir, See this...

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

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

It could solve the problem.

[padders]

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.

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.

[Lowaiy]

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...

[Lowaiy]

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.

[padders]

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.

[padders]

I think if you read Technet Forums thread again you will see that Laurent provided workaround for versions prior to 9.2.0.5.

CURRENT_TIMESTAMP - TRUNC (CAST (CURRENT_TIMESTAMP AS DATE))

[Lowaiy]

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.

[padders]

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