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

Question

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

Taryn · Accepted Answer · 2013-05-29 20:58:42Z

8

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)

edited May 29, 2013 at 20:58

answered May 29, 2013 at 20:22

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ethan Pelton Over a year ago

This runs, but the results are not affected by the parameter. All rows are returned, including those with a createDate greater than the date entered. I've tried changing the style of the date to 101 from 120 as I'm entering the date as mm/dd/yyy Thanks for your help.

Taryn Over a year ago

@EthanPelton Instead of executing the query, try printing the query string and execute it separately. Do you get correct results? What is the value of the @startdate you are submitting? What format?

Ethan Pelton Over a year ago

lol!!! Brilliant bluefeet! Thank you. I had to add escaped single qoutes... createDate > ''' + convert(varchar(10), @startdate, 120) + '''

Taryn Over a year ago

@EthanPelton You are right, I missed the single quotes as well. :)

Chris H · Accepted Answer · 2013-05-29 20:55:45Z

0

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

answered May 29, 2013 at 20:55

Chris H

5452 silver badges7 bronze badges

2 Comments

rbedger Over a year ago

Better yet, add an optional BIT parameter that indicates Debug mode. Then you can check 'IF @Debug=1' and PRINT your @query. Your screen is going to get messy quick if you SELECT a bunch of dynamic queries.

Chris H Over a year ago

Good idea. I generally do a print for the statement that's giving me problems.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related