question

In this example I show the intermediate steps to show, but you don't need to include these... declare @YourTable table (StartTimeText varchar(10), EndTimeText varchar(10)) insert into @YourTable select '70500', '71002' select *, cast( substring(right('000000'+StartTimeText,6),1,2) + ':' + substring(right('000000'+StartTimeText,6),3,2) + ':' + substring(right('000000'+StartTimeText,6),5,2) as datetime), cast( substring(right('000000'+EndTimeText,6),1,2) + ':' + substring(right('000000'+EndTimeText,6),3,2) + ':' + substring(right('000000'+EndTimeText,6),5,2) as datetime), cast( cast( substring(right('000000'+EndTimeText,6),1,2) + ':' + substring(right('000000'+EndTimeText,6),3,2) + ':' + substring(right('000000'+EndTimeText,6),5,2) as datetime) - cast( substring(right('000000'+StartTimeText,6),1,2) + ':' + substring(right('000000'+StartTimeText,6),3,2) + ':' + substring(right('000000'+StartTimeText,6),5,2) as datetime) as time) as ElapsedTime from @YourTable gives StartTimeText EndTimeText ElapsedTime ------------- ----------- ----------------------- ----------------------- ---------------- 70500 71002 1900-01-01 07:05:00.000 1900-01-01 07:10:02.000 00:05:02.0000000

This should run on any version. --First the integer version DECLARE @YourTable TABLE (StartTime int, EndTime int) INSERT INTO @YourTable SELECT 70500, 71002 INSERT INTO @YourTable SELECT 120501, 121845 SELECT StartTime, EndTime, CONVERT(CHAR(8), (DATEADD(SECOND, DATEDIFF(SECOND, CONVERT(DATETIME,CONVERT(VarCHAR(02),Starttime/10000)+':' +CONVERT(VARCHAR(02),(StartTiME/100)%100)+':' +CONVERT(VARCHAR(02),StartTime%100),8), CONVERT(DATETIME,CONVERT(VarCHAR(02),Endtime/10000)+':' +CONVERT(VARCHAR(02),(EndTime/100)%100)+':' +CONVERT(VARCHAR(02),EndTime%100),8)), CONVERT(DATETIME, '00:00:00', 113))),8) AS ElapsedTime FROM @YourTable --And now the string version DECLARE @YourTable TABLE (StartTimeText VARCHAR(10), EndTimeText VARCHAR(10)) INSERT INTO @YourTable SELECT '70500', '71002' INSERT INTO @YourTable SELECT '120501', '121845' SELECT StartTimeText, EndTimeText, CONVERT(CHAR(8), (DATEADD(SECOND, DATEDIFF(SECOND, CONVERT(DATETIME, STUFF(STUFF(LEFT('00', 6 - LEN(StartTimeText)) + StartTimeText, 5, 0, ':'), 3, 0, ':'), 8), CONVERT(DATETIME, STUFF(STUFF(LEFT('00', 6 - LEN(EndTimeText)) + EndTimeText, 5, 0, ':'), 3, 0, ':'), 8)), CONVERT(DATETIME, '00:00:00', 113))), 8) AS ElapsedTime FROM @YourTable

The measurement is a daily query. The activity is measured with the getdate() of that dady only so it all will be under 24 hours. The users are pulling orders within a time period. if anything could check if > 1800 since they would never go past that time daily.

I just noticed the time is not string but it is being stored as int.

So now I get this error when I change the variables to int Msg 242, Level 16, State 3, Line 10 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. That error matches the query I am using for the real data too

Even though you are storing as an int, they are not really integers, as you can't have a value of 76666, for example. They are a string of numbers that represent a time. So going back to my example, simply convert the ints to varchars, and carry on... declare @YourTable table (StartTimeText int, EndTimeText int) insert into @YourTable select '70500', '71002' select *, cast( substring(right('000000'+cast(StartTimeText as varchar),6),1,2) + ':' + substring(right('000000'+cast(StartTimeText as varchar),6),3,2) + ':' + substring(right('000000'+cast(StartTimeText as varchar),6),5,2) as datetime), cast( substring(right('000000'+cast(EndTimeText as varchar),6),1,2) + ':' + substring(right('000000'+cast(EndTimeText as varchar),6),3,2) + ':' + substring(right('000000'+cast(EndTimeText as varchar),6),5,2) as datetime), cast( cast( substring(right('000000'+cast(EndTimeText as varchar),6),1,2) + ':' + substring(right('000000'+cast(EndTimeText as varchar),6),3,2) + ':' + substring(right('000000'+cast(EndTimeText as varchar),6),5,2) as datetime) - cast( substring(right('000000'+cast(StartTimeText as varchar),6),1,2) + ':' + substring(right('000000'+cast(StartTimeText as varchar),6),3,2) + ':' + substring(right('000000'+cast(StartTimeText as varchar),6),5,2) as datetime) as time) as ElapsedTime from @YourTable

How can I get the AVERAGE now? I tried to wrap it all with AVG() but of course errored out: SELECT AVG(CONVERT(varchar(8),cast( cast( substring(right('000000'+cast(rptime as varchar),6),1,2) + ':' + substring(right('000000'+cast(rptime as varchar),6),3,2) + ':' + substring(right('000000'+cast(rptime as varchar),6),5,2) as datetime) - --Do the math on the rptime(End) and rfotime(start) time to get the elapsed time cast( substring(right('000000'+cast(rfotime as varchar),6),1,2) + ':' + substring(right('000000'+cast(rfotime as varchar),6),3,2) + ':' + substring(right('000000'+cast(rfotime as varchar),6),5,2) as datetime) as time))) AS AvgTimePick