Convert Seconds to Minutes

Question

Convert Seconds to Minutes

craig.jenkins

SSCommitted

Points: 1643
More actions
August 20, 2019 at 9:58 am

#3672679
Guys, is there a way to add a new column to show HH-MM-SS ( as per below example). In excel for example i would simply divide the seconds by 86400 and change the format in the column to hh-ss-mm.
```
IF OBJECT_ID('tempdb..#Log') IS NOT NULL DROP TABLE #Log
SELECT * INTO #Log FROM (VALUES 
( '10', 'Dave', '64131', '92'),
( '11', 'Jim', '64132', '238'),
( '12', 'Steve', '64133', '185')) d 

( SEQ, NAME, REF, Seconds)

SELECT * FROM #Log
```

Viewing 5 posts - 1 through 4 (of 4 total)

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

DesNorton SSC-Insane Points: 24381 More actions · Answer 1

Why are your seconds stored as strings? Convert them to integers, then this should do the trick.

SELECT  *, [HH-MM-SS] = CAST(DATEADD(SECOND, l.Seconds, 0) AS time(0))
FROM    #Log AS l;

How to post data/code on a forum to get the best help.

craig.jenkins SSCommitted Points: 1643 More actions · Answer 2

Perfect thank you so much still learning and I really appreciate it. Just one last question, if i had another Second column ie 'Second2' how would i show the total for both columns in minutes as per below.

craig.jenkins SSCommitted Points: 1643 More actions · Answer 3

Its ok, i think i can do something like. Thanks for your time

CAST(DATEADD(SECOND, Seconds + Second2, 0) AS time(0)) as count

drew.allen SSC Guru Points: 76985 More actions · Answer 4

DesNorton wrote:

Why are your seconds stored as strings? Convert them to integers, then this should do the trick.
SELECT  *, [HH-MM-SS] = CAST(DATEADD(SECOND, l.Seconds, 0) AS time(0))
FROM    #Log AS l;

I prefer a slightly different formula since you are really only interested in the time component. There's no reason to depend on an implicit conversion to datetime.

SELECT  *, [HH-MM-SS] = DATEADD(SECOND, l.Seconds, CAST('00:00:00' AS time(0)))
FROM    #Log AS l;

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA