Oracle PL SQL Negative minutes converted to negative HH:mm-CodePudding

The code uses either a negative or positive integer of minutes I have been using the below code successfully for a while, but then realised that if the input number of minutes is less than -60 (ie -15) the hh:mm value returned is positive and not negative. I can understand why this doesnt work, but am struggling to come up with an alternative. Normally the values entered are above 60 or -60 which always ensures the hour value is the correct sign.

Select nvl(-15,0) mins, decode(to_char(trunc(-15/60),'9999') || ':' ||ltrim(to_char(mod(abs(-15),60), '00')),':','00:00', to_char(trunc(-15/60),'9999') || ':' ||ltrim(to_char(mod(abs(-15),60), '00'))) hrs_mins from dual

I am expecting/wanting to see -15 and -00:15

CodePudding user response：

I guess you need another check - whether value you passed to this piece of code is positive or negative (see line #7). Also, I modified format model from 9999 to 9900.

SQL> SELECT NVL (&&value, 0) mins,
  2         DECODE (
  3               TO_CHAR (TRUNC (&&value / 60), '9900')
  4            || ':'
  5            || LTRIM (TO_CHAR (MOD (ABS (&&value), 60), '00')),
  6            ':', '00:00',
  7               CASE WHEN &&value < 0 THEN '-' END
  8            || TO_CHAR (TRUNC (&&value / 60), 'FM9900')
  9            || ':'
 10            || LTRIM (TO_CHAR (MOD (ABS (&&value), 60), '00'))) hrs_mins
 11    FROM DUAL;
Enter value for value: -15

      MINS HRS_MINS
---------- ----------
       -15 -00:15

SQL> undefine value
SQL> /
Enter value for value: 25

      MINS HRS_MINS
---------- ----------
        25 00:25

SQL>

CodePudding user response：

Much simpler:

with
  test_data (mins) as (
    select    10 from dual union all
    select    60 from dual union all
    select   150 from dual union all
    select     0 from dual union all
    select  null from dual union all
    select   -15 from dual union all
    select  -120 from dual union all
    select  -150 from dual union all
    select -1587 from dual
  )
select mins,
       to_char(sign(mins) * (trunc(abs(mins) / 60)   mod(abs(mins), 60) / 100)
              , 'fm99999990d00', 'nls_numeric_characters = :,') as hours_mins
from   test_data
;
 


      MINS HOURS_MINS  
---------- ------------
        10 0:10        
        60 1:00        
       150 2:30        
         0 0:00        
                       
       -15 -0:15       
      -120 -2:00       
      -150 -2:30       
     -1587 -26:27

In your attempt you convert null to 0; if you have a good reason for that, you can do it here too. I didn't; in most cases null doesn't (or shouldn't) mean "zero".