bquintana (2/19/2013)

---

ok, so I used this query-

DECLARE@strColumns VARCHAR(MAX)

DECLARE@strSQL VARCHAR(MAX)

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL

DROP TABLE #tmp_table

CREATE TABLE #tmp_table

(

CallDateTimeDATETIME,

HistIDINT

)

INSERT #tmp_table( CallDateTime, HistID )

Select'11/1/2012 9:02:30.000',2270647UNION ALL

Select'11/1/2012 8:59:59.000',2270648UNION ALL

Select'11/1/2012 9:01:33.000',2270650UNION ALL

Select'11/1/2012 9:03:40.000',2270652UNION ALL

Select'11/1/2012 9:02:50.000',2270653UNION ALL

Select'11/1/2012 9:06:31.000',2270661UNION ALL

Select'11/1/2012 9:31:08.000',2270706UNION ALL

Select'11/1/2012 9:40:24.000',2270719

SELECT@strColumns = COALESCE( @strColumns + .000,',.000', '' )

+ ' SUM( CASE WHEN DATEPART( HOUR, CallDateTime ) = ' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + ' AND DATEPART( MINUTE, CallDateTime ) < 30 THEN 1 ELSE 0 END ) AS ['

+ CAST( 8 + Numbers.number AS VARCHAR(3) ) + '_' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + '30], ' + CHAR(13)

+ ' SUM( CASE WHEN DATEPART( HOUR, CallDateTime ) = ' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + ' AND DATEPART( MINUTE, CallDateTime ) >= 30 THEN 1 ELSE 0 END ) AS ['

+ CAST( 830 + Numbers.number * 100 AS VARCHAR(4) ) + '_' + CAST( 9 + Numbers.number AS VARCHAR(3) ) + ']' + CHAR(13)

FROM(

SELECTnumber

FROMmaster..spt_values

WHEREtype = 'P' AND number < 12

) Numbers --===You can use a Tally table instead of this if you have one

SET@strSQL = ' SELECTCONVERT(VARCHAR(20), CallDateTime, 110) AS Date, '

+@strColumns

+ ' FROM#tmp_table '

+ ' GROUP BY CONVERT(VARCHAR(20), CallDateTime, 110) '

EXECUTE( @strSQL )

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL

DROP TABLE #tmp_table

__

I get an error messsage

Msg 241, Level 16, State 1, Line 13

Conversion failed when converting date and/or time from character string.