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.

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.

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.

Quote:

---

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

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;

---

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.

Presumably you expect us to guess the datatypes of F_TIME_IN_OUT, F_MARGIN_IN_OUT_AFTER and F_TXN_IN_OUT?

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

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>

---

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

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

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

Yea, right...
*******************************************************************
SQL> create or replace FUNCTION test101 RETURN interval day to second is
2 v interval day to second;
3 BEGIN
4 select CURRENT_TIMESTAMP - TRUNC (CAST (CURRENT_TIMESTAMP AS DATE)) into v from dual;
5 end;
6 /

Function created.

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