Concatenate Date in sql Dynamic query

Question

I am trying to execute a dynamic query in which I am concatenating a date but failed in doing

DECLARE @pStartDate datetime  
DECLARE @pEndDate datetime
DECLARE @query nvarchar(MAX)

Dynamic query1

set @query = 'Select * from Table1 From tblEvent
              Where (EventDate Between' + @pStartDate + ' and ' + @pEndDate +')'
Exec(@query)

Error

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

Dynamic query2

set @query = 'Select * from Table1 From tblEvent
              Where (EventDate Between' + cast(@pStartDate as varchar) + ' and ' + cast(@pEndDate as varchar) +')'
Exec(@query)  

Error

Incorrect syntax near 1 [1 stands for whatever date I passed to @pStartDate]

Please suggest me how to do it.

Thanks.

Answer 1

The really proper way to do this would be to use a parametrized query and having sp_executeSql execute this:

DECLARE @pStartDate datetime  
DECLARE @pEndDate datetime

DECLARE @query nvarchar(MAX)

SET @pStartDate = '20080301'
SET @pEndDate = '20080331'

-- if you're setting a NVARCHAR variable - **DO USE** the N'..' prefix!
SET @query = N'SELECT * FROM dbo.Table1
               WHERE OrderDate BETWEEN @StartDate AND @EndDate'

-- execute the dynamic SQL, with a list of parameters, and their values    
EXEC sp_executesql @query, 
                   N'@StartDate DATETIME, @EndDate DATETIME', 
                   @StartDate = @pStartDate, @EndDate = @pEndDate

In that case, there's no fiddling around with string concatenation and missing quotes and messy stuff like that - just a clear, properly parametrized query that isn't vulnerable to SQL injection attacks, and that performs much better since it's execution plan can be reused for subsequent executions.

Answer 2

Add single quote.

Because date or string specify in single quote like this '12-01-2014'.

set @query = 'Select * from Table1 From tblEvent
Where (EventDate Between''' + @pStartDate + ''' and ''' + @pEndDate +''')'