CONVERT DECIMAL values to HH:MM:SS

• Jody Claggett-376930

  Ten Centuries

  Points: 1152

  More actions

  September 3, 2008 at 6:22 pm

  #190098

  Need some help converting DECIMAL values to HH:MM:SS format. I can do this in excel with the DECIMAL based values, but don't know how to get it done in SQL.

  Examples of values in table:

  0.03464120

  0.01630787

  0.02482638

  Can anyone help?

  _______________________________
  [font="Tahoma"]Jody Claggett
  SQL Server Reporting Analyst[/font][/size]

• Gary Johnson-259336

  SSCommitted

  Points: 1673

  More actions

  September 3, 2008 at 7:58 pm

  #866911

  try this...

  IF OBJECT_ID('tempdb..#Foo') IS NOT NULL

  DROP TABLE #Foo

  CREATE TABLE #Foo

  (

  DecimalData decimal(10,9)

  )

  INSERT INTO #Foo VALUES(0.03464120)

  INSERT INTO #Foo VALUES(0.01630787)

  INSERT INTO #Foo VALUES(0.02482638)

  SELECT

  CONVERT(datetime, DecimalData) dateformat,

  CONVERT(varchar,CONVERT(datetime, DecimalData), 108) timeformat

  FROM #Foo

  Note that SQL Server will always display a DateTime field as your default date time format. So to display just the time you have to then convert it to a string.

  Gary Johnson
  Sr Database Engineer

• Jody Claggett-376930

  Ten Centuries

  Points: 1152

  More actions

  September 3, 2008 at 10:18 pm

  #866943

  Exactly what I was looking for. Thanks Gary.

  _______________________________
  [font="Tahoma"]Jody Claggett
  SQL Server Reporting Analyst[/font][/size]