OraFAQ Forum: SQL & PL/SQL » How to convert Hours into Minutes

Home » SQL & PL/SQL » SQL & PL/SQL » How to convert Hours into Minutes

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to convert Hours into Minutes [message #34545]

Sun, 19 December 2004 19:29

M Kumar
Messages: 38
Registered: August 2004

Member

Hi All,

I am having a varchar2 field where i am storing time in
HH24:MI format.

How to convert the time stored in the varchar2 field into Minutes.

If 01:20 is there then the result should be 80 min.

Thanks in advance.
Kumar

Report message to a moderator

Re: How to convert Hours into Minutes [message #34546 is a reply to message #34545]

Sun, 19 December 2004 20:32

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

There are various ways. I have demonstrated two of them below. The first one converts it to a date, then converts that to seconds since midnight, then divides by 60 to get the minutes. The second one takes the substring before the colon and multiplies by 60 and adds that to the substring after the colon.

scott@ORA92> -- test data:
scott@ORA92> column your_column format a11
scott@ORA92> select your_column from your_table
  2  /

YOUR_COLUMN
-----------
01:20
22:37

cott@ORA92> select to_char (to_date (your_column, 'hh24:mi'), 'sssss') / 60 as minutes
  2  from   your_table
  3  /

   MINUTES
----------
        80
      1353

scott@ORA92> select substr (your_column, 1, instr (your_column, ':') - 1) * 60
  2  	    + substr (your_column, instr (your_column, ':') + 1) as minutes
  3  from   your_table
  4  /

   MINUTES
----------
        80
      1353

Report message to a moderator

i know how to convert minutes to hours and minutes eg: 90 minutes to 1 hour 30 m [message #34782 is a reply to message #34546]

Mon, 03 January 2005 02:25

Harikrishnan Ganapathi
Messages: 1
Registered: January 2005

Junior Member

pls give the query to convert minutes to hours and minutes eg: 90 minutes to 1 hour and 30 minutes

Report message to a moderator

Re: i know how to convert minutes to hours and minutes eg: 90 minutes to 1 hour [message #34791 is a reply to message #34782]

Mon, 03 January 2005 10:31

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

-- test data:

scott@ORA92> desc test_tab
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 MINUTES                                                        NUMBER

scott@ORA92> select * from test_tab
  2  /

   MINUTES
----------
        90
        91
         2
       143
      1439

5 rows selected.

-- method 1:

scott@ORA92> column hrs_and_mins format a12
scott@ORA92> select minutes,
  2  	    to_char (trunc (sysdate) + (minutes / (24 * 60)), 'hh24:mi') as hrs_and_mins
  3  from   test_tab
  4  /

   MINUTES HRS_AND_MINS
---------- ------------
        90 01:30
        91 01:31
         2 00:02
       143 02:23
      1439 23:59

5 rows selected.

-- method 2:

scott@ORA92> column hrs_and_mins format a12
scott@ORA92> select minutes,
  2  	    lpad (trunc (minutes / 60), 2, '0') || ':'
  3  	    || lpad (round (mod (minutes, 60)), 2, '0') as hrs_and_mins
  4  from   test_tab
  5  /

   MINUTES HRS_AND_MINS
---------- ------------
        90 01:30
        91 01:31
         2 00:02
       143 02:23
      1439 23:59

5 rows selected.

Report message to a moderator

Previous Topic:	Partial Data Update from a Text file
Next Topic:	Error when Inner query calls Function - Please help, urgent

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Aug 16 04:32:12 CDT 2025