CODEWITHSUNDEEP
oracle-database
VarYaz
VarYaz

Reputation: 141

Converting Integer into time

I am using Oracle 12c version. I have a column which is integer data type and it represents total minutes. I need to convert it into HOURS:MINUTES format.

Could someone suggest how to do that?

Thanks, Venu

Upvotes: 0

Views: 95

Answers (2)

nitin.sharma0180
nitin.sharma0180

Reputation: 481

You can use the to_date built-in function to translate the integer into a valid time.

Example 

SQL> select to_char ( to_date ( '2300', 'HH24MI'), 'HH:MI AM') integer_time from dual;

Upvotes: 0

Jacobm001
Jacobm001

Reputation: 4539

This is a fairly easy thing to accomplish using a series of built in functions.

to_char(floor(field / 60) || ':' || to_char(mod(field, 60), 'FM00')

Breaking it down:

  1. Get the number of hours: to_char(floor(field / 60)
    • field / 60 gets us the number of hours
    • floor() ensures that we don't have to deal with decimals
    • to_char() converts it to a string so we can concatenate it.
  2. Add the ':' || ':' ||
  3. Get the number of minutes
    • mod(field, 60) gets the remainder of field / 60
    • to_char() again converts to a string for the concat step
    • 'FM00' ensures that we keep our leading zeros.

Obviously, you'll need to replace field with whatever the field in your database is called.

Upvotes: 3

Related Questions