×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Convert seconds into hh mm SS
2

Convert seconds into hh mm SS

Convert seconds into hh mm SS

(OP)
Hi

I have a filed that have seconds in them.

I want to dispaly one field just as mm:ss and another as HH:MM:SS

I cannot seem to get the formula to do this despite many googles. Convert seems to the way but I cannot get it to work with a field name.

Filed names are setduration and Realduration. I am bringing the table into a view, could someone advise what code to write to do this please.

Many Thanks

RE: Convert seconds into hh mm SS

Display of data/numbers in a particular format is typically the responsibility of the application layer. If you are storing the number of seconds, you should be able to divide by 86400 (number of seconds in a day) to get a datetime value. For instance 2400 seconds:


CODE --> SQL

SELECT Convert(datetime, 2400.000/(86400))
Results
1900-01-01 00:39:59.997 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Convert seconds into hh mm SS

CODE

DECLARE @Seconds int
SET @Seconds = 3260


SELECT LEFT(CONVERT(TIME, DATEADD(ss, @Seconds,0)),5),
       LEFT(CONVERT(TIME, DATEADD(ss, @Seconds,0)),8) 

Borislav Borissov
VFP9 SP2, SQL Server

RE: Convert seconds into hh mm SS

(OP)
Hi

Sorry don't quite understand, how do I apply this to the field names within the view.

For example One entry the field Realduration. is 1526 which should equate to 000:25:26

Thanks


RE: Convert seconds into hh mm SS

Date/Time is just a number (in units of DAYS) that can be formatted into something that looks like a date/time value like 0:25:26

So, your seconds value of 1526, divided by 86400 (sec per day) equals 0.017662037 days which can be converted to or formatted as 0:25:26.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Convert seconds into hh mm SS

(OP)
Hi

I understand the conversion but how do I get it into a line of code for a specific field

select ActualDuration, CONVERt (datetime, 2400.000/(86400))
from WTTReportProcessSteps

Gives me results but they are not right. It si the coding I cannot work out.

ActualDuration
Result
1460
1900-01-01 00:40:00.000
477
1900-01-01 00:40:00.000

RE: Convert seconds into hh mm SS

What do you see if you try this or one of the other solutions:

CODE --> SQL

SELECT ActualDuration, CONVERT(datetime, [ActualDuration]/(86400)) AS NewDuration
FROM WTTReportProcessSteps 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Convert seconds into hh mm SS

CODE

SELECT LEFT(CONVERT(TIME, DATEADD(ss, Realduration,0)),5),
       LEFT(CONVERT(TIME, DATEADD(ss, Realduration,0)),8) 
FROM YourTable 

Borislav Borissov
VFP9 SP2, SQL Server

RE: Convert seconds into hh mm SS

(OP)
Hi

Dhoohom I get the attached results when I run the code you sent. I would like the result to just shown in HH:MM:SS if possible. Any ideas , thanks

RE: Convert seconds into hh mm SS

(OP)
Hi again

I used bborissov code and it as worked perfect for so thanks to all.

SELECT LEFT(CONVERT(TIME, DATEADD(ss, Realduration,0)),5),
LEFT(CONVERT(TIME, DATEADD(ss, Realduration,0)),8)
FROM YourTable

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close