Sql query works normally, but fails when dynamic

I have a query, which works when its run like this:

  declare @nvRecipients varchar(4000)
    ,@CustomerCode varchar(6)
    ,@start datetime
    ,@end datetime

 SELECT @CustomerCode = '10095'
       ,@start = '01/01/2011开发者_StackOverflow'
       ,@end = '02/01/2011'

  Select substring(PSD.DTSItemCode,1,4) As ItemCompanyCode , 
  convert(varchar(50),Cast(Sum(PSD.Quantity) as money),1) As TotalShipped 
  From  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipHeader PSH 
  Inner Join  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipsDetail PSD On PSH.PKSNumber = PSD.PKSNumber 
  Where Cast ( PKSDate As DateTime ) >= @start
    And Cast ( PKSDate As DateTime ) <= @end
    And PSD.Quantity > 0  
    And ( CompanyCode = @CustomerCode)
   Group By  substring(PSD.DTSItemCode,1,4) 
   Order By  substring(PSD.DTSItemCode,1,4) 

But throws the error

Syntax error converting datetime from character string.

When ran like this within a stored procedure where @customercode, @start, and @end are supplied parameters:

       Set @nvQuery = ' Select substring(PSD.DTSItemCode,1,4) As ItemCompanyCode , '
  Set @nvQuery = @nvQuery + ' convert(varchar(50),Cast(Sum(PSD.Quantity) as money),1) As TotalShipped ' 
  Set @nvQuery = @nvQuery + ' From  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipHeader PSH '
  Set @nvQuery = @nvQuery + ' Inner Join  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipsDetail PSD On PSH.PKSNumber = PSD.PKSNumber '
  Set @nvQuery = @nvQuery + ' Where Cast ( PKSDate As DateTime ) >= ''' + @start + ''' '
  Set @nvQuery = @nvQuery + '   And Cast ( PKSDate As DateTime ) <= ''' + @end + ''' '
  Set @nvQuery = @nvQuery + '   And PSD.Quantity > 0 '  
  Set @nvQuery = @nvQuery + '   And ( CompanyCode = ''' + @CustomerCode + ''') '
  Set @nvQuery = @nvQuery + '  Group By  substring(PSD.DTSItemCode,1,4) '
  Set @nvQuery = @nvQuery + '  Order By  substring(PSD.DTSItemCode,1,4) '

Can anyone see my error? I can't seem to find it. The data all checks out with ISDATE().

If they're dates, the you need to cast them to varchar to append to the query.

You cannot concatenate a datetime to a string w/o casting it first:

Set @nvQuery = @nvQuery + '   And Cast( PKSDate As DateTime) <= ''' + CAST(@end AS VARCHAR(20)) + ''' '

  Set @nvQuery = ' Select substring(PSD.DTSItemCode,1,4) As ItemCompanyCode , '
  Set @nvQuery = @nvQuery + ' convert(varchar(50),Cast(Sum(PSD.Quantity) as money),1) As TotalShipped ' 
  Set @nvQuery = @nvQuery + ' From  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipHeader PSH '
  Set @nvQuery = @nvQuery + ' Inner Join  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipsDetail PSD On PSH.PKSNumber = PSD.PKSNumber '
  Set @nvQuery = @nvQuery + ' Where Cast ( PKSDate As DateTime ) >= ''' + CONVERT(varchar(20), @start, 101) + ''' '
  Set @nvQuery = @nvQuery + '   And Cast ( PKSDate As DateTime ) <= ''' + CONVERT(varchar(20), @end, 101) + ''' '
  Set @nvQuery = @nvQuery + '   And PSD.Quantity > 0 '  
  Set @nvQuery = @nvQuery + '   And ( CompanyCode = ''' + @CustomerCode + ''') '
  Set @nvQuery = @nvQuery + '  Group By  substring(PSD.DTSItemCode,1,4) '
  Set @nvQuery = @nvQuery + '  Order By  substring(PSD.DTSItemCode,1,4) '

To save yourself a lot of trouble, you can use parameters with dynamic SQL as well, yes! - parameters, just like in your real query.

  Set @nvQuery = ' Select substring(PSD.DTSItemCode,1,4) As ItemCompanyCode , '
  Set @nvQuery = @nvQuery + ' convert(varchar(50),Cast(Sum(PSD.Quantity) as money),1) As TotalShipped ' 
  Set @nvQuery = @nvQuery + ' From  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipHeader PSH '
  Set @nvQuery = @nvQuery + ' Inner Join  [AVANTISERVER\NCL_MASTER].AVANTI.dbo.PackingSlipsDetail PSD On PSH.PKSNumber = PSD.PKSNumber '
  Set @nvQuery = @nvQuery + ' Where Cast ( PKSDate As DateTime ) >= @start '
  Set @nvQuery = @nvQuery + '   And Cast ( PKSDate As DateTime ) <= @end '
  Set @nvQuery = @nvQuery + '   And PSD.Quantity > 0 '  
  Set @nvQuery = @nvQuery + '   And ( CompanyCode = @CustomerCode) '
  Set @nvQuery = @nvQuery + '  Group By  substring(PSD.DTSItemCode,1,4) '
  Set @nvQuery = @nvQuery + '  Order By  substring(PSD.DTSItemCode,1,4) '

Instead of running it via

EXEC (@nvQuery)

use the form

exec sp_executeSQL @nvQuery,
     N'@start datetime,@end datetime,@customerCode varchar(6)', --list of params
     @start, @end, @customerCode -- params, matching list

This gets around all sorts of string manipulation, cast/format issues. e.g.

declare @start datetime,@end datetime,@customerCode varchar(6)
select @start = getdate(), @end = getdate()+2, @customerCode = 'TEST'

set @nvQuery ... -- build the statement

exec sp_executeSQL @nvQuery,
     N'@start datetime,@end datetime,@customerCode varchar(6)', --list of params
     @start, @end, @customerCode -- params, matching list