How do i round off time to nearest hour and minute using Oracle sql query for hcm-CodePudding

Input Data is of my

My query is like this which I am trying

‘”’ || INTEGER_PART(Accrual_Balance) || ‘:’|| 
FORMAT_NUMBER(ROUND(DECIMAL_PART(round(Accrual_Balance,2))*.01*60,2)
,’00’) 
 ||‘”’

Output Should be like below

14.30
248.35
15.00
-3.46

Please rectify my query which I developed. For example, whenever there are 3 decimals such as 14.997, it should be in the file as 15:00. The 3rd decimal needs to round up. Example 2: If it was 14:499 it should come in the file as 14:30 for half an hour.

CodePudding user response：

A similar solution:

WITH dat AS
(
  SELECT 14.499  AS Accrual_Balance FROM DUAL UNION ALL
  SELECT 248.589    FROM DUAL UNION ALL
  SELECT 14.997     FROM DUAL UNION ALL
  SELECT -3.7594185 FROM DUAL
)
SELECT CASE WHEN ROUND(ABS(60*(Accrual_Balance- TRUNC(Accrual_Balance)))) = 60 THEN TO_CHAR(CEIL(Accrual_Balance))||':00'
            ELSE TO_CHAR(TRUNC(Accrual_Balance))|| ':' || TO_CHAR(ROUND(ABS(60*(Accrual_Balance- TRUNC(Accrual_Balance)))))
       END
  FROM dat;