using TRUNC in timestamps

[Lowaiy]

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.

[padders]

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

SELECT EXTRACT (HOUR FROM current_timestamp)
FROM   dual;

...but that is still a type conversion.

[Lowaiy]

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.

[jmodic]

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

[padders]

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

SELECT current_timestamp - TRUNC (current_timestamp)
FROM   dual;

[Lowaiy]

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.

[padders]

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

[Lowaiy]

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

[padders]

Hmmmm. Works fine for me.

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>

[Lowaiy]

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