Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Seconds to minutes 2

Status
Not open for further replies.

Cpreston

MIS
Mar 4, 2015
972
GB
Hi

I have a field that the results show in seconds. I want to display this in minutes and seconds but unsure how to do this.

downtime AS Downtime is the filed name and the result for example on one record is 2299.35800027848

I want that to show the result 2299.35800027848 / 60 which I believe results in 38.32

Please advised and many thanks in advance
 
Select ...downtime/60 As Downtime...

Notice that the decimals are not seconds in that case, i.e. 38.32 means about 38 minutes and 20 seconds, as .33 is about a third minute.

It all becomes much simpler, if you have two datetimes and use the TIME datetype and convert a datetime difference to that:
Code:
Declare @Date1 DATETIME = '2024-01-01 10:00:00.022'
Declare @Date2 DATETIME = '2024-01-01 10:10:10.000'
Select CONVERT(TIME,@Date2 - @Date1) as ElapsedTime

So within a query that has two datetime fields available
Code:
Select CONVERT(TIME,Atable.EndDateTime - Atable.StartDateTime) as ElapsedTime

But if you merely want to divide a decimal/float number that's seconds to mintue you just divide by 60, as you know and SQL includes math operations on fields, so as iitially said, simply [tt]downtime/60 as fieldnameyouwant[/tt], as simple as that.

Chriss
 
Hi

I managed to get most sorted but I am stuck on one row of coding

(CAST([batch end] AS float) - CAST([batch start] AS float)) * 24 * 3600 - (downtime + [pntr time]) AS Runtime

This gives me a result of 25098.5339996806 however I need to divide this by 60 to get 418.30. I have tried to put in the /60 in several places but none work, any ideas how achieve this

Thanks
 
I have a field that the results show in seconds. I want to display this in minutes and seconds but unsure how to do this.

SELECT CONVERT(varchar, DATEADD(ss, @Seconds, 0), 108)
Should give you HH:MM:SS

... where @Seconds is your number of seconds.
 
Managed it thanks (CAST([batch end] AS float) - CAST([batch start] AS float)) * 24 * 3600 /60 - (downtime/60 + [pntr time]/60)
 
Cpreston,

if in doubt, put everything into brackets and divide that by 60 as last operation by PEMDAS or BODMAS, whatever you remember, if any...

Code:
((CAST([batch end] AS float) - CAST([batch start] AS float)) * 24 * 3600 - (downtime + [pntr time]))/60 AS Runtime
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top