error converting character to smalldatetime type 1

[CompCodeBoy]

Having errors wheyn writing the quer below in Query Analyzer

Declare @strSQL varchar(1200)
Declare @datestr smalldatetime

set @strSQL= 'select * from table1 where date_field > ''12/12/2004'''

EXEC (strSQL)

The above works just fine. date_field is of type smalldatetime in table1

How I can get the above to work by replacing 12/12/2004 with @datestr ??? I tried casting and much more. Nothing seems to work. Any ideas?

 

[amrita418]

Here is two ways of doing this.

DECLARE 
    @strSQL  varchar(1200),
    @datestr smalldatetime

    SET @datestr = '12/12/2005'
    SET @strSQL= 'select * from Table1 where date_field > ''' + convert(varchar(10), @datestr, 101) + ''''

    --print @strSQL
    EXEC (@strSQL)

Or declaring the @datestr as varchar to get rid of the additional convert function.

DECLARE 
    @strSQL  varchar(1200),
    @datestr varchar(10)

    SET @datestr = '12/12/2005'
    SET @strSQL= 'select * from Table1 where date_field > ''' + @datestr + ''''

    --print @strSQL
    EXEC (@strSQL)

Regards,
AA

 

[CompCodeBoy]

Both work. AA, thanks for your help. I really appreciate it. you're good