Stored procedure SQL statement

[quisar]

I have a stored procedure to which an int value numdays is passed. The following is the code in the SP.

declare @purgedate datetime,
@command varchar(1000),
@database varchar(128),
@numrows int,

select @purgedate = dateadd(dd, -1 * @numdays + 1, getdate())

select @command = 'select count(*) from ' + @database + '..MSmerge_contents a, ' +
@database + '..MSmerge_genhistory b where a.generation = b.generation and ' +
'b.coldate < ' + convert(char(10),@purgedate,101)
select @command

The select statement above gives me a problem.
The result I want is
&quot;Select count(*) from TESTDB..msmerge_contents a, TESTDB..MSmerge_genhistory b where a.generation = b.generation and b.coldate < '10/31/2002'. but the above gives
&quot;Select count(*) from TESTDB..msmerge_contents a, TESTDB..MSmerge_genhistory b where a.generation = b.generation and b.coldate < 10/31/2002
and the results for both are not same. The date also has the timepart which i want to ignore. I am using SQL 2000. Using ' and &quot; is not clear to me in sql statements exp.

Please help.

Quisar

 

[rt63]

Try with the changes highlighted

declare @purgedate datetime,
@command varchar(1000),
@database varchar(128),
@numrows int,

select @purgedate = dateadd(dd, -1 * @numdays + 1, getdate())

select @command = 'select count(*) from ' + @database + '..MSmerge_contents a, ' +
@database + '..MSmerge_genhistory b where a.generation = b.generation and ' +
'b.coldate < ' + char(39)+convert(char(10),@purgedate,101) +char(39)
select @command
RT

 

[quisar]

It worked fine. Thanks RT.

Quisar