TSQL -- Inserting Dates Into Dynamic SQL

Question

Consider the following TSQL:

SET @WhereClause1 = 'where a.Date > ' + @InvoiceDate

I get a date/string conversion error. @InvoiceDate is a datetime variable. What is the right syntax?

Answer 1

This might work.

SET @WhereClause1 = 'where a.Date > ''' + convert(varchar, @InvoiceDate) + ''''

although an error will be raised if the value is null.

Answer 2

This will work:

SET @WhereClause1 = 'where a.Date > ''' + cast(@InvoiceDate as varchar(100)) + ''''