score:1

Accepted answer

Try to keep your data in variables of the appropriate type, whenever possible.

For instance, here you can do:

--@From and @To are declared previously as datetimes
set @mySql ='select * from abc where (MyDATE between @From and @To)'

--Other code that constructs/works on @mySQL

--Finally, run the dynamic sql:
EXEC sp_executesql @mySql,
                   '@From datetime,@To datetime`,
                   @From,
                   @To

And everything should work beautifully because you're not forcing back and forth between strings and datetimes, and its those conversions that introduce the opportunity to have formatting issues.

Read More

score:0

the issue here is that when you are building the Dynamic SQL, you are looking to cast your parameters as DateTime.

What you should actually do is avoid the use of casting. Set the Parameters as date time and store required values before you use them to build your dynamic SQL Statement.

score:2

The only "correct" way to do this is to preserve them as parameters inside the dynamic SQL. For example:

set @mySql =N'select * from abc where MyDATE between @from and @to';

exec sp_executesql @mySql, N'@from datetime, @to datetime', @fromOuter, @toOuter;

This keeps them correctly typed in the dynamic code, and avoids both formatting concerns and SQL injection risks. Note that the names inside and outside the dynamic code do not need to match, as shown in the example above (@from and @to are the names in the dynamic code; @fromOuter and @toOuter are the names in the calling code).

Note that it doesn't matter if you pass in more parameters than you actually use (this would be pretty normal for a dynamic filtering method).


More questions with similar tag