How to convert HHMMSS to seconds using T-SQL-CodePudding

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:

163135 (16:31:35) becomes 59495 (seconds)

CodePudding user response：

Meanwhile I figured out this formula:

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

CodePudding user response：

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.