This executes correctly: (It is weird that I needed to use '' by the date for it to actually execute)
DECLARE
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.statcolumnname) FROM [85137_PHY_Long_PG] c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SELECT statdate, ' + @cols + ' from
(
select statdate, statcolumnname, statcolumnvalue
from [85137_PHY_Long_PG]
) x
pivot
(
min(statcolumnvalue)
for statcolumnname in (' + @cols + ')
) p WHERE statdate BETWEEN ''2012-04-01 12:15:00'' AND ''2012-04-01 12:45:00'' ORDER BY statdate'
execute(@query)
Now I want to replace the dates with variables:
DECLARE
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@from AS NVARCHAR(MAX),
@to AS NVARCHAR(MAX);
set @from = '2012-04-01 12:15:00'
set @to = '2012-04-01 12:45:00'
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.statcolumnname) FROM [85137_PHY_Long_PG] c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SELECT statdate, ' + @cols + ' from
(
select statdate, statcolumnname, statcolumnvalue
from [85137_PHY_Long_PG]
) x
pivot
(
min(statcolumnvalue)
for statcolumnname in (' + @cols + ')
) p WHERE statdate BETWEEN ''+@from+'' AND ''+@to+'' ORDER BY statdate'
execute(@query)
I get the following error:Conversion failed when converting character string to smalldatetime data type
Changing the where statement to the following:
WHERE statdate BETWEEN ''+convert(smalldatetime,@from)+'' AND ''+convert(smalldatetime,@to)+'' ORDER BY statdate'
Still gives me the same error, just can't seem to replace the dates as variables
'' is not weird; it is a notation that enables apostrophes inside varchars.
When concatenating make sure that you are not trying to concatenate anything other than (n)varchars and (n)chars because Sql Server will attempt to convert them to other datatypes; in your case, in smalldatetime. You might avoid this trouble by explicitly converting your parameter dates to nvarchars before/during concatenation, but better solution is to use sp_executesql and parameters.
If you leave parameters inside query:
You can execute it with parameters:
Where @from_variable and @to_variable are datetime variables defined earlier in batch.
UPDATE:
If your ultimate goal is to wrap this code in stored procedure, here is a template: