0

I have the following query that returns a total seconds value (up to 86400 seconds (1 day))

I want to convert this to an HH:MM:SS value. Any help is appreciated.

(SELECT FIRST TSS.TotalDrivingTime 
 FROM HLG01_TR_TachographShiftSummary TSS
 WHERE TSS.DriverID = TRD.DriverID
   AND (CONVERT(date, TSS.PeriodStart)) = S.LoadDate)
jarlh
  • 42,561
  • 8
  • 45
  • 63
John Mitchell
  • 1
  • Does this answer your question? [How to get a date in YYYY-MM-DD format from a TSQL datetime field?](https://stackoverflow.com/questions/889629/how-to-get-a-date-in-yyyy-mm-dd-format-from-a-tsql-datetime-field) – maru Feb 09 '22 at 14:35
  • 1
    [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Feb 09 '22 at 14:53
  • 1
    `cast(TSS.TotalDrivingTime as interval hour to second(0))` - the ANSI SQL way! – jarlh Feb 09 '22 at 16:08

2 Answers2

0

in sqlserver you can use:

declare @Seconds as int = 86400; SELECT CONVERT(time(0), DATEADD(SECOND, @Seconds, 0)) as 'hh:mm:ss'

and in mysql

SELECT SEC_TO_TIME(test_duration) as `Time` FORM YOUR_TABLE;

or

FROM_UNIXTIME(unix_timestamp, '%h:%i:%s')

How to convert time in seconds to HH:MM:SS format in MySQL?

https://www.sqlservercentral.com/forums/reply/1459738

MajidGh
  • 51
  • 6
  • This SELECT clause is a sub-query embedded in a larger query. I'm struggling to get the syntax exactly correct. I've tried:- (CONVERT(time(0), DATEADD(SECOND,SELECT FIRST TSS.TotalDrivingTime FROM HLG01_TR_TachographShiftSummary TSS WHERE TSS.DriverID = TRD.DriverID AND (CONVERT(date, TSS.PeriodStart))= S.LoadDate)) AS 'hh:mm:ss', – John Mitchell Feb 09 '22 at 14:57
0

you can use this

(Format(Int(Sum([Seconds])/3600),'00') & ':' & Format(Sum([Seconds]/86400),'nn:ss')) AS [Total Time]
jarlh
  • 42,561
  • 8
  • 45
  • 63
Ushakiran Dhoke
  • 11