Convert a duration varchar into minutes

• Kal Elali

  Valued Member

  Points: 60

  More actions

  November 17, 2005 at 5:03 pm

  #166642

   Hi Guys n Gals

  I desperatley need your help. I have a field which gives the duration for a problem as 10h 51m. I need to convert it to show 651 as interger. Could someone please explain the best way to do this.

   

  Thanks in advance...

   

  Kal...

• rsharma

  SSCarpal Tunnel

  Points: 4865

  More actions

  November 17, 2005 at 10:34 pm

  #604560

  There are couple of ways...one is:

  declare @hr int, @min-2 int, @string varchar(20)

  set @string = '10h 51m'

  set @hr = charindex('h', @string)

  set @min-2 = charindex('m', @string)

  select substring(@string, 1, @hr -1) * 60 + substring(@string, @hr + 1, @min-2 - (@hr + 1))

  --output

  651

  Another test:

  declare @hr int, @min-2 int, @string varchar(20)

  set @string = '100h 45m'

  set @hr = charindex('h', @string)

  set @min-2 = charindex('m', @string)

  select substring(@string, 1, @hr -1) * 60 + substring(@string, @hr + 1, @min-2 - (@hr + 1))

  --output

  6045

   

• prasad mamillapalli

  Grasshopper

  Points: 23

  More actions

  November 18, 2005 at 7:48 am

  #604633

  if this oracle you can do like this

  select

  substr('10h',1,instr('10h','h')-1) *60 + substr('51m',1,instr('51m','m')-1) from dual