CODEWITHSUNDEEP
sql-serverdatetime
Kashif
Kashif

Reputation: 3327

date filtering in dynamic SQL

set @vSqlFilters = '(ABRA.START_DATE !> ' + cast(@dEndDate as varchar(26)) + ' or ABRA.END_DATE !< ' + cast(@dStartDate as varchar(26)) + ')';

I want my parameters @dStartDate and @dEndDate to keep the quotes around them. Currently, they're typed in manually by the end user with the quotes around them. Right now, I just get

(ABRA.START_DATE !> 2013-05-01 or ABRA.END_DATE !< 2011-04-05) in my query instead of 

(ABRA.START_DATE !> '2013-05-01' or ABRA.END_DATE !< '2011-04-05').

Since this is thrown into a dynamic SQL query, I need my @dEndDate and @dStartDate to be cast as strings. Also I want it to treat my dStartDate and dEndDate as dates not integers. I just don't see how I can get SQL to compare my dates.

Thanks!

Upvotes: 0

Views: 1503

Answers (1)

ErikE
ErikE

Reputation: 50201

Put doubled-up single quotes anywhere within the statement you want a single quote to appear:

set @vSqlFilters = '(ABRA.START_DATE !> ''' + cast(@dEndDate as varchar(26))
+ ''' or ABRA.END_DATE !< ''' + cast(@dStartDate as varchar(26)) + ''')';

However, there is no need to do this. With sp_executesql you can just use parameters:

DECLARE @SQL nvarchar(max);
SET @SQL = 'SELECT * FROM dbo.ABRA ABRA WHERE ABRA.START_DATE <= @dEndDate'
+ ' AND ABRA.END_DATE >= @dStartDate';
EXEC sp_executesql @SQL, N'@dStartDate datetime, @dEndDate datetime',
@dStartDate, @dEndDate;

This is, in my mind, better practice. It also may offer better performance through execution plan reuse (or it may not).

I would also like to recommend that you NOT use inequality operators !, !<, and !>. They make it hard for others to read and comprehend your code. use <>, >=, and <=.

Last, are you sure you wanted OR in your filter? I may be missing something.

Upvotes: 1

Related Questions