Seconds to minutes 2

[Cpreston]

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

 

[Chris Miller]

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:

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

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

 

[Cpreston]

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

 

[gbaughma]

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.

 

[Cpreston]

Managed it thanks (CAST([batch end] AS float) - CAST([batch start] AS float)) * 24 * 3600 /60 - (downtime/60 + [pntr time]/60)

 

[Chris Miller]

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...

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