CODEWITHSUNDEEP
sqlsql-servert-sql
Ethan Pelton
Ethan Pelton

Reputation: 1796

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

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 + '

Upvotes: 3

Views: 19231

Answers (2)

Taryn
Taryn

Reputation: 247610

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)

Upvotes: 8

Chris H
Chris H

Reputation: 545

When you dynamically build the SQL Statement, the date value needs to be wrapped in single quotes. Whenever building a dynamic statement, do a SELECT @query and make sure the results look correct.

For your example, you would need to have 'WHERE createdate > ''' + covert(varchar(10), @startdate, 111) + '''

That would output: WHERE createdate > '2013/05/29'

Without the single quotes you would have: WHERE createdate > 2013/05/29

Upvotes: 0

Related Questions