I am struggling with this query which returns the error: Conversion failed when converting date and/or time from character string.
This is a common error judging from my google searches, but nothing I've tried so far works. I've tried casting @startdate as datetime and varchar and leaving it alone, as in the below example.
I've also tried using convert against the fieldname and the parameter name, although admittedly, I may just be getting the syntax wrong.
ALTER PROCEDURE [dbo].[customFormReport]
(
@formid int,
@startdate DATETIME
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(fieldname) from FormResponse WHERE FormID = @formid AND value IS NOT NULL FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT FormID, FormSubmissionID,' + @cols + ' from
(
SELECT FormID, FormSubmissionID, fieldname, value
FROM FormResponse WHERE FormID = ' + CAST(@formid AS VARCHAR(25)) + ' AND createDate > ' + @startdate + '
) x
pivot
(
max(value)
for fieldname in (' + @cols + ')
) p '
execute(@query)
edit: the query works except when I add the bit causing the error:
' AND createDate > ' + @startdate + '
Best Answer
The problem is you are attempting to concatenate a
datetime
to yourvarchar
sql string. You need to convert it:So the full code will be: