Need a function to convert duration to any meaningful time measurement

• Mark Shvarts 1

  SSCertifiable

  Points: 5702

  More actions

  October 23, 2006 at 4:20 pm

  #170751

  In msdb.dbo.sysjobhistory they have duration not in seconds or minutes, but in a format like 'ddhhmmss'. Does somebody have a function that converts it to seconds or minutes ?

  Thanks

• Chris Chilvers

  SSCrazy

  Points: 2260

  More actions

  October 24, 2006 at 5:08 am

  #667528

  First, is it stored as text or a number?

• Mark Shvarts 1

  SSCertifiable

  Points: 5702

  More actions

  October 24, 2006 at 8:16 am

  #667589

  It's a number. For example 101 means 1 min 1 sec.

• Jason Irwin

  SSC Veteran

  Points: 255

  More actions

  October 24, 2006 at 9:00 am

  #667607

  I'm sure there's a better way ... but here's one solution:

  CREATE FUNCTION fn_GetSeconds(@hhmmss int)

  RETURNS int

  As

  Begin

  DECLARE @j-2 varchar(6)

  SET @j-2 = right('000000' + cast(@hhmmss as varchar(6)), 6)

  RETURN substring(@j, 1, 2) * 24 * 60 + substring(@j, 3, 2) * 60 + substring(@j, 5, 2)

  End

  Go