CODEWITHSUNDEEP
sql-server
owl
owl

Reputation: 154

How to convert HHMMSS to seconds using T-SQL

SQL server table msdb.dbo.sysjobhistory returns run_time and run_duration as INTEGER value formatted as HHMMSS.

How to convert it to seconds?

Example:

Upvotes: 0

Views: 674

Answers (2)

Stuck at 1337
Stuck at 1337

Reputation: 2084

You can use modulo and integer division to separate the hours, minutes, and seconds, multiply by number of seconds in each result, then sum.

DECLARE @hms int = 163135;

SELECT @hms / 10000 * 3600
     + @hms % 10000 / 100 * 60
     + @hms % 100;

59495

To use this as a view, it's really not any different:

CREATE VIEW dbo.viewname
AS
  SELECT <other cols>, run_duration, 
    run_duration_s = run_duration / 10000 * 3600
                   + run_duration % 10000 / 100 * 60
                   + run_duration % 100
  FROM msdb.dbo.sysjobhistory
  WHERE ...

If you don't like math so much, you can treat it like a string:

DECLARE @hms int = 163135;

DECLARE @s char(6) = RIGHT(CONCAT('000000', @hms), 6);

SELECT LEFT(@s, 2) * 60 * 60
  + SUBSTRING(@s, 3, 2) * 60
  + RIGHT(@s, 2);

59495

However, this latter solution may need some tweaking if you could have durations > 99 hours, since now the string will be 7 digits. Maybe safer to use:

DECLARE @hms int = 163135;

DECLARE @s char(24) = RIGHT(CONCAT(REPLICATE('0',24), @hms), 24);

SELECT LEFT(@s, 20) * 60 * 60
  + SUBSTRING(@s, 21, 2) * 60
  + RIGHT(@s, 2);

24 is a ludicrous example, but safe. The job would have had to start in 1990 to hit 10 digits today.

Upvotes: 2

owl
owl

Reputation: 154

Meanwhile I figured out this formula:

SELECT DATEDIFF(SECOND, '00:00:00', FORMAT(run_duration, '00:00:00'))
FROM msdb.dbo.sysjobhistory

Upvotes: 1

Related Questions