sql - 从字符串转换日期和/或时间时转换失败

Question

我正在努力处理返回错误的查询:从字符串转换日期和/或时间时转换失败。

从我的谷歌搜索来看，这是一个常见的错误，但到目前为止我没有尝试过任何工作。我已经尝试将 @startdate 转换为 datetime 和 varchar 并保持不变，如下例所示。

我也尝试过对字段名和参数名使用 convert，虽然我承认，我可能只是语法错误。

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)

编辑:查询有效，除非我添加导致错误的位:

' AND createDate > ' + @startdate + '

Answer 1

问题是您试图将 datetime 连接到您的 varchar sql 字符串。你需要转换它:

convert(varchar(10), @startdate, 120)

所以完整的代码是:

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)