declare @Start char(6), @End char(6)set @Start = '133737'set @End = '154040'declare @StartTime datetime, @EndTime datetimeset @StartTime = stuff(stuff(@Start,3,0,':'),6,0,':')set @EndTime = stuff(stuff(@End,3,0,':'),6,0,':')select @StartTime, @EndTime, convert(char(8), @EndTime - @StartTime, 108)
select convert(char(8), EndDate - StartDate, 108)
/* I'm assuming here that @TimeString1 always represents the earlier time and @TimeString2 the later time */DECLARE @TimeString1 char(6)DECLARE @TimeString2 char(6)SELECT @TimeString1 = '133737', @TimeString2 = '154040' /* Insert ':' characters to format as 'HH:MM:SS' and implicitly convert strings to datetime values */DECLARE @Time1 datetimeDECLARE @Time2 datetimeSELECT @Time1 = STUFF(STUFF(@TimeString1, 5, 0, ':'), 3, 0, ':'), @Time2 = STUFF(STUFF(@TimeString2, 5, 0, ':'), 3, 0, ':') /* Determine time difference as a datetime value relative to value 0 (1900-01-01 00:00:00.000). Note that if @Time1 > @Time2 then the result of the DATEDIFF function call is negative. In this case, it is assumed that the two times span a day boundary so 24 * 60 * 60 = 86400 seconds need to be added to get the true elapsed time. */DECLARE @ElapsedTime datetimeSELECT @ElapsedTime = DATEADD(second, DATEDIFF(second, @Time1, @Time2) + CASE WHEN (@Time1 > @Time2) THEN 86400 ELSE 0 END, 0) /* Finally display elapsed time using CONVERT format 108 */SELECT CONVERT(char(8), @ElapsedTime, 108)
select convert(char(8), convert(datetime, convert(datetime, stuff(stuff('154040',3,0,':'),6,0,':')) - convert(datetime, stuff(stuff('133737',3,0,':'),6,0,':'))), 108)
select convert(char(8),convert(datetime,convert(datetime, stuff(stuff(right('0' + '154040',6),3,0,':'),6,0,':')) - convert(datetime, stuff(stuff(right('0' + '133737',6),3,0,':'),6,0,':'))),108)
select convert(char(8),convert(datetime,convert(datetime, stuff(stuff(right('000000' + '154040',6),3,0,':'),6,0,':')) - convert(datetime, stuff(stuff(right('000000' + '133737',6),3,0,':'),6,0,':'))),108)