time format conversion

• ckmoied

  Ten Centuries

  Points: 1092

  More actions

  November 26, 2007 at 6:17 am

  #62196

  if datediff is used to give no of milliseconds b/w dateA and dateB. How can this be converted in 00:00:00 format. I don't want to display date.

• R2ro

  Default port

  Points: 1439

  More actions

  November 26, 2007 at 6:34 am

  #754001

  Try this:

  declare

  @dateA datetime,

  @dateB datetime,

  @baseDate datetime

  set @dateA = '11/26/2007 13:27:53.205'

  set @dateB = '11/26/2007 13:27:53.209'

  set @baseDate = '07/04/1923 00:00:00.000'

  select convert(char(12), dateadd(ms, datediff(ms, @dateA, @dateB), @baseDate), 114)

  Bear in mind that the minimum difference will be 3 ms (as demo'd above)...

  HTH,

  Art

• ckmoied

  Ten Centuries

  Points: 1092

  More actions

  November 26, 2007 at 8:58 am

  #754108

  Thanks R2ro

• R2ro

  Default port

  Points: 1439

  More actions

  November 26, 2007 at 9:11 am

  #754113

  Just so's ya know...

  The date chosen for the base date (@baseDate) is unimportant except that it must be in range. The important part is the time component of the base date. You may want to consider selecting a 'standard' base date (e.g. 1/1/1900 or 1/1/1753) until SQL2008 is available. It has separate (and various) date and time datatypes.

• ckmoied

  Ten Centuries

  Points: 1092

  More actions

  November 27, 2007 at 4:21 am

  #754460

  Thanks R2ro

  Appreciate your support.

• Chandra Sekhara Vyas Dhara

  SSChampion

  Points: 10207

  More actions

  November 27, 2007 at 4:51 am

  #754475

  declare

  @dateA datetime,

  @dateB datetime,

  @baseDate datetime

  set @dateA = '11/26/2007 13:27:53.205'

  set @dateB = '11/26/2007 13:27:53.209'

  set @baseDate = '07/04/1923 00:00:00.000'

  select convert(char(11),dateadd(ms, datediff(ms, @dateA, @dateB), @baseDate),114)