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.

marc_s · Accepted Answer · 2014-08-13 05:30:08Z

3

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.

answered Aug 13, 2014 at 5:30

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Over a year ago

+1 Also, here's a SO answer with similar info (and a SQL Fiddle).

Sathish · Accepted Answer · 2014-08-13 05:13:17Z

1

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 +''')'

answered Aug 13, 2014 at 5:13

Sathish

4,4854 gold badges36 silver badges60 bronze badges

Collectives™ on Stack Overflow

Concatenate Date in sql Dynamic query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related