CODEWITHSUNDEEP
sqlsql-server-2008
jedgard
jedgard

Reputation: 868

how to add a date variable in sql string

I have a query that is being built based on some data and I need to be able to add the @StartDate parameter in the string but I get the following error

Conversion failed when converting date and/or time from character string.

Part of the query is like this:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @where = ''
...

SET @where = @where + '(initDate BETWEEN ' + @StartDate + ' AND ' + @EndDate + ')'

How can I add the StartDate and EndDate there without causing this issue? I tried CONVERT(DATETIME, @StartDate) , but get the same issue

Upvotes: 1

Views: 2649

Answers (2)

Andy Nichols
Andy Nichols

Reputation: 3002

Just use CAST or CONVERT to change @StartDate and @EndDate to a string. Choose an appropriate method as per the link that gives you the level of precision you need.

Upvotes: 0

Santosh
Santosh

Reputation: 12353

Try this 

SET @where = @where + '(initDate BETWEEN ' + convert(varchar(10),@StartDate,104) + ' AND ' + convert(varchar(10),@EndDate,104) + ')'

OR 

SET @where = @where + '(initDate BETWEEN ' + convert(varchar(10),@StartDate,106) + ' AND ' + convert(varchar(10),@EndDate,106) + ')'

Upvotes: 2

Related Questions