Conversion failed when converting date and/or time from character string
17,134
The problem is you are attempting to concatenate a datetime
to your varchar
sql string. You need to convert it:
convert(varchar(10), @startdate, 120)
So the full code will be:
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 > ''' + convert(varchar(10), @startdate, 120) + '''
) x
pivot
(
max(value)
for fieldname in (' + @cols + ')
) p '
execute(@query)
Author by
tintyethan
Updated on June 23, 2022Comments
-
tintyethan about 2 years
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 + '