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?

Pulley

Answer 1

This might work.

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

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

Scotopia

this doesn't work because you have to enclosed the date (inside the string) with quotation marks – Tatman

Answer 2

This will work:

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

Tatman

i wish I could mark two answers approved because this one works too. thanks – Pulley

When I have that trouble, I select one and give points (arrow up) to the others (if they are more than 1). That doesn't mean you have to do the same :) – Tatman

Answer 3

Since your composing query as a string first, then I think you need to convert @InvoiceDate to a string with something like this. http://www.databasejournal.com/features/mssql/article.php/10894_2197931_1/Working-with-SQL-Server-DateTime-Variables-Part-Two---Displaying-Dates-and-Times-in-Different-Formats.htm

Australia

Answer 4

... and you will probably need to enclose date strings in quotes.

It would probably actually be better to construct the date string in the calling routine because you should be checking there for null values and maybe other validations.

Kiaochow

Answer 5

EXEC sp_executesql N'SELECT * FROM Orders WHERE a.Date > @date',
                   N'@date datetime',
                   @date = @InvoiceDate

Aspergillosis