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

     


  • ♿ (Parody)

    @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:
    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?
    @Rhywden said:
    @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.

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


  • ♿ (Parody)

    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 hh🇲🇲ss, 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')

Log in to reply