We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Change duration in seconds into hh:mm:ss format

allicia
allicia asked
on
Medium Priority
579 Views
Last Modified: 2008-07-03
Hi there,

I have a table A with column duration (int) and I would like to view the duration (initially in seconds) in hh:mm:ss format.

Example,

Table A
---------
duration       Expression1
120             00:02:00
110             00:01:50
45               00:00:45
3602           01:00:02

What is the sql statement to convert the seconds into the mentioned format (hh:mm:ss)?


:O)
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011

Commented:
select convert(char(8),dateadd(s,duration,'19000101'),108)

so add the duration to 1st jan 1900
then convert it back to character using format 108 which is just time....

should work as long as duration is less than 24hours...

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Oops,
Sorry Lowfatspread for duplicate post

Commented:
@allicia,
Lowfatspread was the first to come with the solution,
mine is exactly the same although the syntax slightly differs
'19000101' is the 'root' date and is stored internally as 0
I feel miserable for getting the points that LFS deserved

@LFS
my apologies again

@both
I'm OK for the points to be reaffected
Or I can post a "points for LFS" question

Thks

Hilaire
CERTIFIED EXPERT
Top Expert 2011

Commented:
no problem  hilaire
;-)

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.