total duration

• sqlpro

  Hall of Fame

  Points: 3859

  More actions

  January 14, 2004 at 7:56 pm

  #107131

  i have a column in a table which contains data duration like

  following 1d (means 1 day) ,1 w (means 1 week),5h (means 5 hours )etc.,

  how can i write query which gives me total duration in following format

  d:HH:mm:ss

  any ideas please

• wz700

  SSCertifiable

  Points: 7133

  More actions

  January 14, 2004 at 8:48 pm

  #490131

  convert total duration to seconds.

  use dateadd and convert function with 108 style to get HH:mm:ss, then use datediff to get days.

  Here is sample.

  declare @sec int

  declare @base datetime

  select @sec = 2117210, @base='1900-1-1'

  select cast(datediff(d, @base, dateadd(ss,@sec,@base))as varchar)+':'

   +convert(varchar, dateadd(ss,@sec,@base),108)

  It results

  24:12:06:50

• sqlpro

  Hall of Fame

  Points: 3859

  More actions

  January 14, 2004 at 9:03 pm

  #490132

  Thank you for your idea.

  in that case then i need strip out chars from data

  (i mean w,d,h) and convert them into seconds and then

  run ur query .

  is that what u r trying 2 say ?

  cheers