CONVERT DECIMAL values to HH:MM:SS

Question

Post reply

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]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 1

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 · Answer 2

Exactly what I was looking for. Thanks Gary.

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