Syntax error converting datetime from character string in dynamic SQL statement

[ravi]

Hi, I'm getting the
"Syntax error converting datetime from character string."

for the below query.
I have some request like this query and need to know the correct sql statement for this to avoid this error.



use northwind
go
declare @reqdate datetime
,@shipdate datetime
set @reqdate='1996-05-05'
set @shipdate='1996-06-05'
declare @cmd varchar(1000)
set @cmd='select * from orders where requiredate >='+@reqdate+' AND shipdate >='+@shipdate
exec @cmd

[chetanjain04]

try the foll. script:

use northwind
go
declare @reqdate datetime
,@shipdate datetime
set @reqdate='1996-05-05'
set @shipdate='1996-06-05'
declare @cmd varchar(1000)
set @cmd='select * from orders where requiredate >='+convert(varchar(11),@reqdate,121)+' AND shipdate >='+convert(varchar(11),@shipdate,121)
exec @cmd

in case of any queries, pl. reply with the table structure.

[MAK]

set quoted_identifier off
use northwind
go
declare @reqdate datetime,@shipdate datetime
set @reqdate='1996-05-05'
set @shipdate='1996-06-05'
declare @cmd varchar(1000)
set @cmd='select * from orders where requireddate >="'+convert(varchar(23),@reqdate)+'" AND shippeddate >="'+convert(varchar(23),@shipdate)+'"'
exec (@cmd)