CODEWITHSUNDEEP
oracle-database
user1736265
user1736265

Reputation: 11

how to convert minutes into DD:HH:MM format in oracle? Let's say 1008307 seconds I need to display as DD:HH:MM format

how to convert seconds into (days/hours/minutes) DD:HH:MM format in oracle? Let's say 1008307 seconds I need to display as DD:HH:MM format

Upvotes: 1

Views: 3495

Answers (2)

Justin Cave
Justin Cave

Reputation: 231671

If I understand what you want (my guess is different than @cagcowboy's)

SQL> ed
Wrote file afiedt.buf

  1  select extract( day from ds_interval ) || ':' ||
  2         to_char( extract( hour from ds_interval ), 'fm00' ) || ':' ||
  3         to_char( extract( minute from ds_interval ), 'fm00' ) "dd:hh:mm format",
  4         to_char( extract( second from ds_interval ), 'fm00' ) remaining_seconds
  5    from (
  6  select numtodsinterval( 1008307, 'second' ) ds_interval
  7    from dual
  8* )
SQL> /

dd:hh:mm format                                  REM
------------------------------------------------ ---
11:16:05                                         07

Upvotes: 2

cagcowboy
cagcowboy

Reputation: 30848

If I understand what you want (not sure I do!) try this:

select to_char(start_date + (1008307 / 86400), 'DD:HH24:MI') from dual;

I assume you need to say days/hours/minutes since a certain time - this is the start_date. And you'll need to change the 1008307 to whatever value, obviously.

Upvotes: 3

Related Questions