Convert Decimal to Date

Question

I'm getting an output like 15.73 as decimal and I want it to be converted to a time format. 15.73 is basically something like 15:43. How can I achieve this result? Below is my query:

SELECT ((TRUNC(lt.date_start + 1) - 1/(24*60*60)) - lt.date_start)*24 DUR, lt.date_start 
from losttime lt
where lt.date_start >= to_date('02/07/2020 08:00' , 'dd/mm/yyyy HH24:MI')

and currently the output is coming in decimals as attached.

Answer 1

Your current calculation gets you an integer number of hours and a fractional representation of seconds; however, when working with Oracle dates, numbers added to dates represent an integer number of days and a fractional representation of time within the day. The conversion is simple enough - divide your value by 24, or in this case simply eliminate the multiplication by 24. That gives you a fractional day value. Now, to format this as HH:MI you need to convert it to a valid date, and the simplest thing to do here (since you don't actually care about the year/month/day values) is to just add your computed value to TRUNC(SYSDATE). Doing this converts your value to an Oracle DATE, and from there you can use TO_CHAR to get the HH:MI as a formatted string:

SELECT TO_CHAR(TRUNC(SYSDATE) +
                 (TRUNC(lt.DATE_START + 1) - INTERVAL '1' SECOND - lt.DATE_START), 'HH24:MI') DUR,
       lt.DATE_START
  FROM LOSTTIME lt
  WHERE lt.DATE_START >= TO_DATE('02/07/2020 08:00' , 'DD/MM/YYYY HH24:MI')

db<>fiddle here

Answer 2

When I want to calculate the ELAPSED_TIME in format HH:MI, I use this query

SQL> select * from my_test ;

C1                  C2
------------------- -------------------
2020-07-26 01:03:51 2020-07-26 19:03:51
2020-07-26 10:08:45 2020-07-26 14:38:45
2020-07-26 11:39:10 2020-07-26 13:54:10


SQL> select c1,c2,
TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(c2-c1, 'DAY')), 'FM00')
   || ':' ||
 TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(c2-c1, 'DAY')), 'FM00')
  as elapsed_time  from my_test ;   2    3    4    5

C1                  C2                  ELAPSED
------------------- ------------------- -------
2020-07-26 01:03:51 2020-07-26 19:03:51 18:00
2020-07-26 10:08:45 2020-07-26 14:38:45 04:30
2020-07-26 11:39:10 2020-07-26 13:54:10 02:15

SQL>

If you want the elapsed time with seconds

SQL> select c1,c2,
  2  TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(c2-c1, 'DAY')), 'FM00')
   || ':' ||
 TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(c2-c1, 'DAY')), 'FM00')
  3    4    5     || ':' ||
  6   TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(c2-c1, 'DAY')), 'FM00') as elapsed_time
 from my_test ;  7

C1                  C2                  ELAPSED_TIM
------------------- ------------------- -----------
2020-07-26 01:03:51 2020-07-26 19:03:51 18:00:00
2020-07-26 10:08:45 2020-07-26 14:38:45 04:30:00
2020-07-26 11:39:10 2020-07-26 13:54:10 02:15:00

SQL>

Be aware that this query will only work as long as the difference between the end time and the start time is lower than 24 hours.

Answer 3

If you create a function, it makes the final query simpler. Function accepts number of days as a parameter and returns it formatted.

SQL> create or replace
  2     function f_days2ddhhmiss (par_broj_dana  in number)
  3        return varchar2
  4     is
  5        l_broj_dana  number := par_broj_dana;
  6        retval       varchar2 (20);
  7     begin
  8        with podaci
  9             as (select trunc (l_broj_dana) broj_dana,
 10                        round (mod (l_broj_dana * 24, 24), 2) broj_sati,
 11                        round (mod (l_broj_dana * 24 * 60, 60), 2) broj_minuta,
 12                        round (mod (l_broj_dana * 24 * 60 * 60, 60), 2)
 13                           broj_sekundi
 14                   from dual)
 15        select    lpad (trunc (p.broj_sati), 2, '0')
 16               || ':'
 17               || lpad (trunc (p.broj_minuta), 2, '0')
 18          into retval
 19          from podaci p;
 20
 21        return retval;
 22     end f_days2ddhhmiss;
 23  /

Function created.

SQL>

Now, your query (see comments within the code).

SQL> with
  2  -- sample data; you already have that
  3  losttime (date_start) as
  4    (select to_date('07/02/2020 08:16', 'mm/dd/yyyy hh24:Mi') from dual union all
  5     select to_date('07/13/2020 08:24', 'mm/dd/yyyy hh24:mi') from dual),
  6  -- this is your current query; I just removed *24 as function expects number of DAYS, not HOURS
  7  yourquery as
  8    (select ((trunc(lt.date_start + 1) - 1/(24*60*60)) - lt.date_start) dur, lt.date_start
  9     from losttime lt
 10    )
 11  select dur, date_start, f_days2ddhhmiss(dur) result
 12  from yourquery;

       DUR DATE_START RESULT
---------- ---------- ----------
,655543981 02.07.2020 15:43
,649988426 13.07.2020 15:35

SQL>

As the function calculates number of days and seconds as well, you can include them into the output, if you want. I didn't feel like removing them from the function. Or, you can even move that code out of the function and put it to query itself, but it gets somewhat messy (and probably faster).