A simple way to calculate the duration of a running program with a start and end time. The information should be presented in an hour:minutes:seconds format with leading zeroes (i.e., 00:00:00). Two local variables are used to demonstrate the query.
Typically, this is more useful if done as a stored procedure.
DECLARE @StartTime DATETIME = '2013-07-16 13:02:29';
DECLARE @EndTime DATETIME;
DECLARE @Duration INT;
SET @EndTime = '2013-07-16 15:29:31';
SET @Duration = DATEDIFF(second, @StartTime, @EndTime);
SELECT RIGHT('0' + CONVERT(VARCHAR(2), @Duration/3600), 2)
+ ':' + RIGHT('0' + CONVERT(VARCHAR(2), @Duration%3600/60), 2)
+ ':' + RIGHT('0' + CONVERT(VARCHAR(2), @Duration%60), 2)
AS Duration;
The result here is printed with a column heading Duration and a value of 02:27:02.
Further Reading
DECLARE @local_variable (Transact-SQL)
DATEDIFF (Transact-SQL)
