-
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.
-
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).
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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>
**********************************************************************
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
|