A combination of string and math based solutions were posted as potential alternatives. A recent post highlighted the use of the msdb function agent_datetime. All of these math functions posted will provide an accurate second value from a SQL Agent duration without regard to sign (positive or negative) or size (greater than 6 digits).

All of the string methods mentioned, including the agent_datetime function, rely on three facts to be true.

1) All values in the table must either be zero or positive integersWhile I understand that the existence of any of these values is a sign of a larger issue – if the code generating the report fails, the report is incapable of telling me there is a problem. I generally run reports on new environments to understand what the current state is and what needs to be worked on. It is not uncommon to find jobs that have run more than a day in a new environment. While less common, I have also had to deal with negative duration values stored in the sysjobhistory. I agree – neither is ideal and neither should exist. However, it is much more common for me to be called to fix what is broken, not to watch an already stable and perfect environment.

2) No value greater than 235959 can exist in the table because of datetime conversion issues.

3) Positions 2-3 and Positions 4-5 must not be greater than 60 due to datetime conversion issues.

You can examine the T-SQL at the end of this post. At each test, the T-SQL represents a proposed solution on the forum thread. Each test converts each of the integer values in the table to a number of seconds based upon the HHMMSS rule of how these durations are stored in sysjobhistory. These are populated into a variable so that any transfer to the client or IO issues are eliminated in the performance test. The only thing being tested should be CPU and memory throughput. In order to adjust for the performance of any given environment, I establish a control. This control does no conversion on the integer value, simply selects each one into a variable. This should provide a good baseline for what the maximum performance could be.

Given that each of the string functions proposed fail unless provided very specific numeric values, I eliminated any negative values, any values > 235959, and any values where the HH and SS values were > 59 to avoid datetime conversion errors.

Below is the average of each of these methods (and a control) over 10 executions for both my laptop and an enterprise class server.

AvgDurationMS | ||

RunType | Laptop | Server |

235959 Control | 60 | 27 |

235959 Mod Math Implicit | 87 | 38 |

235959 Mod Math | 90 | 44 |

235959 Mod Math Intrope | 123 | 44 |

235959 SQL-Tucker | 163 | 79 |

235959 rmechaber | 182 | 90 |

Full Control | 983 | 406 |

235959 agent_datetime | 1516 | 658 |

Full Mod Math | 1755 | 790 |

Full Mod Math Implicit | 1814 | 867 |

Full Mod Math Intrope | 2377 | 1077 |

Admittedly, the difference in these test scenarios is negligible. However, in all cases, using the mod operator and the implicit truncation within SQL Server was the fastest conversion method. In addition to being the fastest – it’s the most compact visibly and works for just about any values – not just those less than a single day. I encourage you to run these tests in your environment and see which solution comes out on top.

As with anything in SQL Server, when asked which method is “better” I would have to say “It depends.” However, if you ask which appears to be fastest, which works for most conceivable situations, and which will I use? I’m going to stick with math – and leave the strings be.

Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter

USE tempdb

GO

/*

Adapted from

http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable&referringTitle=Home

*/

-- Suppress data loading messages

SET NOCOUNT ON

-- Create Sample Data using a Table Varable

/*

Positive and Negative values

*/

SELECT TOP 3942000 IDENTITY(INT,-1971000,1) AS N

INTO Numbers

FROM sys.all_objects a, sys.all_objects b, sys.all_objects c, sys.all_objects d

-- Create a Primary Key and Clustered Index to control data order

ALTER TABLE dbo.Numbers ADD CONSTRAINT

PK_Numbers PRIMARY KEY CLUSTERED (N)

GO

CREATE TABLE DurationMetrics(

RunType VARCHAR(100) NOT NULL, StartTime DATETIME NOT NULL, EndTime DATETIME NOT NULL)

GO

DECLARE @StartTime DATETIME,

@EndTime DATETIME,

@DurationSeconds int

DECLARE @Loops INT = 10,

@i INT

/*Control*/

SELECT @i = 0

WHILE @Loops > @i

BEGIN

SELECT @StartTime = GETDATE()

SELECT @DurationSeconds = N

FROM Numbers

SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)

SELECT 'Control' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime

END

/*Mod Math*/

SELECT @i = 0

WHILE @Loops > @i

BEGIN

SELECT @StartTime = GETDATE()

SELECT @DurationSeconds =

FLOOR(N/10000) * 3600 +

FLOOR(N/100%100) * 60 +

N%100

FROM Numbers

SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)

SELECT 'Mod Math' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime

END

/*Mod Math Implicit*/

SELECT @i = 0

WHILE @Loops > @i

BEGIN

SELECT @StartTime = GETDATE()

SELECT @DurationSeconds =

N/10000 * 3600 +

N/100%100 * 60 +

N%100

FROM Numbers

SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)

SELECT 'Mod Math Implicit' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime

END

/*Mod Math Intrope*/

SELECT @i = 0

WHILE @Loops > @i

BEGIN

SELECT @StartTime = GETDATE()

SELECT @DurationSeconds =

((N - N % 10000) /10000) * 60 * 60

+ ((N % 10000 - N % 100) /100) * 60

+ N % 100

FROM Numbers

SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)

SELECT 'Mod Math Intrope' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime

END

/*235959 Control*/

SELECT @i = 0

WHILE @Loops > @i

BEGIN

SELECT @StartTime = GETDATE()

SELECT @DurationSeconds = N

FROM Numbers WHERE N BETWEEN 0 AND 235959

AND N%100 < 60 AND N/100%100 < 60

SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)

SELECT '235959 Control' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime

END

/*235959 SQL-Tucker*/

SELECT @i = 0

WHILE @Loops > @i

BEGIN

SELECT @StartTime = GETDATE()

SELECT @DurationSeconds =

datediff(ss,0,cast(stuff(stuff(left('000000',6-len(N))+cast(N AS VARCHAR),5,0,':'),3,0,':') AS DATETIME))

FROM Numbers WHERE N BETWEEN 0 AND 235959

AND N%100 < 60 AND N/100%100 < 60

SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)

SELECT '235959 SQL-Tucker' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime

END

/*235959 Mod Math*/

SELECT @i = 0

WHILE @Loops > @i

BEGIN

SELECT @StartTime = GETDATE()

SELECT @DurationSeconds =

FLOOR(N/10000) * 3600 +

FLOOR(N/100%100) * 60 +

N%100

FROM Numbers WHERE N BETWEEN 0 AND 235959

AND N%100 < 60 AND N/100%100 < 60

SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)

SELECT '235959 Mod Math' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime

END

/*235959 Mod Math Implicit*/

SELECT @i = 0

WHILE @Loops > @i

BEGIN

SELECT @StartTime = GETDATE()

SELECT @DurationSeconds =

N/10000 * 3600 +

N/100%100 * 60 +

N%100

FROM Numbers WHERE N BETWEEN 0 AND 235959

AND N%100 < 60 AND N/100%100 < 60

SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)

SELECT '235959 Mod Math Implicit' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime

END

/*235959 Mod Math Intrope*/

SELECT @i = 0

WHILE @Loops > @i

BEGIN

SELECT @StartTime = GETDATE()

SELECT @DurationSeconds =

((N - N % 10000) /10000) * 60 * 60

+ ((N % 10000 - N % 100) /100) * 60

+ N % 100

FROM Numbers WHERE N BETWEEN 0 AND 235959

AND N%100 < 60 AND N/100%100 < 60

SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)

SELECT '235959 Mod Math Intrope' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime

END

/*235959 rmechaber*/

SELECT @i = 0

WHILE @Loops > @i

BEGIN

SELECT @StartTime = GETDATE()

SELECT @DurationSeconds =

LEFT(RIGHT('000000' + CAST(N AS VARCHAR(6)), 6), 2) *3600 +

Substring(RIGHT('000000' + CAST(N AS VARCHAR(6)), 6), 3, 2) *60 +

RIGHT(RIGHT('000000' + CAST(N AS VARCHAR(6)), 6), 2)

FROM Numbers WHERE N BETWEEN 0 AND 235959

AND N%100 < 60 AND N/100%100 < 60

SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)

SELECT '235959 rmechaber' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime

END

/*235959 agent_datetime*/

SELECT @i = 0

WHILE @Loops > @i

BEGIN

SELECT @StartTime = GETDATE()

SELECT @DurationSeconds =

datediff(s,msdb.dbo.agent_datetime(19000101,0),msdb.dbo.agent_datetime(19000101,N))

FROM Numbers WHERE N BETWEEN 0 AND 235959

AND N%100 < 60 AND N/100%100 < 60

SELECT @EndTime = GETDATE()

SELECT @i = @i + 1

INSERT INTO DurationMetrics (RunType, StartTime, EndTime)

SELECT '235959 agent_datetime' AS RunType, @StartTime AS StartTime, @EndTime AS Endtime

END

SELECT RunType,

AVG(DATEDIFF(ms, StartTime, EndTime)) AS AvgDurationMS,

MIN(DATEDIFF(ms, StartTime, EndTime)) AS MaxDurationMS,

MAX(DATEDIFF(ms, StartTime, EndTime)) AS MaxDurationMS

FROM DurationMetrics

GROUP BY RunType

ORDER BY AvgDurationMS

/*Clean Up*/

DROP TABLE DurationMetrics

DROP TABLE Numbers

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.