Trying to format the result of a numtodsinterval function
-
I am trying to "pretty up" the result of this Oracle phrase:
min (to_char(numtodsinterval((a.update_date - a.transaction_time),'minute'),'DD HH24:MI:SS')) "M01"
The result of this effort was:
+000000000 00:00:00.123456789
It is worse then:
min (numtodsinterval((a.update_date - a.transaction_time),'minute')) "M01" and getting:
0 0:0:0.123456789
The end-users can understand the first format better than the second version.
How to I change the 00:00:00.123456789 to 00:00:00? I thought that was done as part of the TO_CHAR function? I am running 10g and will be switching to 11.2 soon. However, I have to support both for a while.
I appreciate your help on this!
Thank you!
-
TRWTF is Oracle.
-
I don't speak SQL much. What on earth is the meaning of the minimum of a character string?
(Also, shouldn't this thread be on the oracle-specific forum?)
-
@DaveK said:
I don't speak SQL much. What on earth is the meaning of the minimum of a character string?
Do you wonder how to sort them, too?
-
@DaveK said:
I don't speak SQL much. What on earth is the meaning of the minimum of a character string?
(Also, shouldn't this thread be on the oracle-specific forum?)
According to this, it orders the result alphabetically.
-
@boomzilla said:
@DaveK said:
@Rhywden said:I don't speak SQL much. What on earth is the meaning of the minimum of a character string?
Do you wonder how to sort them, too?@DaveK said:
So, the code we were shown is just a clause in a select or similar? Could part of that be messing with the output format?I don't speak SQL much. What on earth is the meaning of the minimum of a character string?
(Also, shouldn't this thread be on the oracle-specific forum?)
According to this, it orders the result alphabetically.
I'd also try putting the min inside the to_char and see if that worked any better...
-
@DaveK said:
I don't speak SQL much. What on earth is the meaning of the minimum of a character string?
Character strings can be sorted. MIN takes the "smallest" (or "first" I guess) of a sortable set.
-
Your best bet may be to use extract:
select numtodsinterval( to_date('10/08/2011 13:00','mm/dd/yyyy hh24:mi') - sysdate, 'day' ) a, extract( day from numtodsinterval( to_date('10/08/2011 13:00','mm/dd/yyyy hh24:mi') - sysdate, 'day' ) ) d, extract( hour from numtodsinterval( to_date('10/08/2011 13:00','mm/dd/yyyy hh24:mi') - sysdate, 'day' ) ) h, extract( minute from numtodsinterval( to_date('10/08/2011 13:00','mm/dd/yyyy hh24:mi') - sysdate, 'day' ) ) m, extract( second from numtodsinterval( to_date('10/08/2011 13:00','mm/dd/yyyy hh24:mi') - sysdate, 'day' ) ) s from dual ;
With 11g, I get:
A D H M S ----------- ---------------------- ---------------------- ---------------------- ---------------------- 0 23:40:22.0 0 23 40 22
You should be able to format that up however you like.
-
@danielrose01 said:
min (to_char(numtodsinterval((a.update_date - a.transaction_time),'minute'),'DD
HH24:MI:SS')) "M01"
If all you want is hours:minutes:seconds and the difference will never be more than a day, you can do this most simply as:
to_char(a.update_date - a.transaction_time + trunc(sysdate), 'HH24:MI:SS')
You can substitute a fixed date for trunc(sysdate), as long as it has no time part.
-
If you do want days as well, say in a format like d hhss, you can use
to_char(trunc(a.update_date - a.transaction_time)) || ' ' || to_char(a.update_date - a.transaction_time + trunc(sysdate), 'HH24:MI:SS')