Eirikur

Good points there. Hence, my new improved version 2. I've padded out TimeDecimal with another two zeros (that deals with the small numbers), then converted to time and back to char(5) (that checks that all times are valid). The latest date arithmetic expression that you posted runs quicker than the string manipulation, but the original one (which returns the data in the format requested by the OP) runs slower, so it's the conversion to varchar that chews up the CPU.

Strangely, if I change one of the TimeDecimal values to 25611322 (which is clearly going to give an invalid time), the date arithmetic produces the seemingly random result '02:01:00.0000000'. The string manipulation produces an error, which is what you'd expect.

Steve, using Eirikur's technique of selecting into a variable takes the cost of dragging the result set across the network out of the equation and means that the size of the gap between the elapsed times is closer to the size of the gap between the CPU times.

Here's the code (the test harness is the same as before).

SET STATISTICS TIME ON

SET STATISTICS IO ON

-- String manipulation

DECLARE @CHARBUCKET char(5);

SELECT

@CHARBUCKET = CAST(CAST(STUFF(LEFT(RIGHT('0000' + CAST(TimeDecimal AS varchar(8)),8),4),3,0,':') AS time) AS char(5))

FROM

#john;

-- Date arithmetic without conversion

DECLARE @TIMEBUCKET TIME;

SELECT

@TIMEBUCKET = DATEADD(MINUTE,(FLOOR((TimeDecimal / 10000) / 100) * 60) + FLOOR((TimeDecimal / 10000) % 100),CAST('00:00' AS TIME))

FROM

#john;

-- Date arithmetic with conversion

DECLARE @CHARTIMEBUCKET char(5)

SELECT

@CHARTIMEBUCKET = CONVERT(VARCHAR(5),DATEADD(MINUTE,(FLOOR((TimeDecimal / 10000) / 100) * 60) + FLOOR((TimeDecimal / 10000) % 100),CAST('00:00' AS TIME)),108)

FROM

#john;

And here are the timings in the same order, after ten executions of each

CPU time = 1109 ms, elapsed time = 1222 ms

CPU time = 1047 ms, elapsed time = 1197 ms

CPU time = 1094 ms, elapsed time = 1208 ms

CPU time = 1000 ms, elapsed time = 1197 ms

CPU time = 1063 ms, elapsed time = 1212 ms

CPU time = 1109 ms, elapsed time = 1303 ms

CPU time = 1172 ms, elapsed time = 1318 ms

CPU time = 1172 ms, elapsed time = 1266 ms

CPU time = 1016 ms, elapsed time = 1270 ms

CPU time = 1156 ms, elapsed time = 1278 ms

CPU time = 860 ms, elapsed time = 1032 ms

CPU time = 860 ms, elapsed time = 993 ms

CPU time = 890 ms, elapsed time = 968 ms

CPU time = 938 ms, elapsed time = 971 ms

CPU time = 875 ms, elapsed time = 1014 ms

CPU time = 953 ms, elapsed time = 1118 ms

CPU time = 891 ms, elapsed time = 1003 ms

CPU time = 828 ms, elapsed time = 1002 ms

CPU time = 797 ms, elapsed time = 967 ms

CPU time = 1187 ms, elapsed time = 1400 ms

CPU time = 1234 ms, elapsed time = 1371 ms

CPU time = 1328 ms, elapsed time = 1353 ms

CPU time = 1141 ms, elapsed time = 1348 ms

CPU time = 1312 ms, elapsed time = 1460 ms

CPU time = 1547 ms, elapsed time = 1665 ms

CPU time = 1344 ms, elapsed time = 1380 ms

CPU time = 1296 ms, elapsed time = 1356 ms

CPU time = 1344 ms, elapsed time = 1487 ms

CPU time = 1234 ms, elapsed time = 1355 ms

CPU time = 1235 ms, elapsed time = 1359 ms

John