Convert seconds into hh mm SS 2

[Cpreston]

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

 

[dhookom]

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:

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

 

[bborissov]

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

 

[Cpreston]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Cpreston]

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

 

[dhookom]

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

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

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

 

[bborissov]

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

 

[Cpreston]

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

 

[Cpreston]

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