CODEWITHSUNDEEP
sqlsql-server
user3017424
user3017424

Reputation: 65

Convert seconds to hours: minutes: seconds

I have seem a few questions already for this but I can't make the adjustments to the SQL I have as I find it complicated when joining other tables.

I have a query that tell me how long a specific job has been running while it is running but it tells me this in seconds. I want to convert those seconds into the format hours:minutes or hours:minutes:seconds.

Thanks!

USE msdb
SELECT job.name AS [Job Name],
       activity.run_requested_date AS [Run Date And Time],
       DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) AS [Elapsed]
FROM sysjobs_view job WITH (NOLOCK)
LEFT JOIN sysjobactivity activity ON job.job_id = activity.job_id
LEFT JOIN syssessions sess ON sess.session_id = activity.session_id
LEFT JOIN (SELECT MAX( agent_start_date ) AS max_agent_start_date
           FROM syssessions WITH (NOLOCK)) sess_max
    ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE run_requested_date IS NOT NULL
  AND stop_execution_date IS NULL

Upvotes: 0

Views: 2898

Answers (1)

TT.
TT.

Reputation: 16137

Take the following example:

DECLARE @dt1 DATETIME='2016-01-11T12:00:00';
DECLARE @dt2 DATETIME='2016-01-07T20:30:15';

SELECT
    CAST(DATEDIFF(s,@dt2,@dt1)/3600 AS VARCHAR(4))+':'+
    CAST(DATEDIFF(s,@dt2,@dt1)%3600/60 AS VARCHAR(2))+':'+
    CAST(DATEDIFF(s,@dt2,@dt1)%3600%60 AS VARCHAR(2));

This will print out 87:29:45 ie 87 hours, 29 minutes and 45 seconds. Use this example and apply it to your query. GL!

Upvotes: 2

Related Questions