Dynamic versus explicit querys in sql-server 2005

Question

Can anybody explain this? Theoretically both are the same query but they give different answers.

a)

declare @date varchar(10)

set date = '03/02/2013'

select count(*) from table1 where (from <= @date) and (@date <= to)

b)

declare @date varchar(10)

set date = '03/02/2013'

set @sqlstring = 'select count(*) from table1 where (from <= ' + @date + ') and (' + @date + ' <= to)' 

exec sp_executeSql @sqlstring

The first set of sentences gives '2' as a result, and this is the correct answer, but in the second set of sentences, I have the same query executed dynamically through a string, but the answer is '0'.

Answer 1

In the first, the effective SQL is

select count(*) from table1 where (from <= '03/02/2013') and ('03/02/2013' <= to)

In the second one, the effective SQL is

select count(*) from table1 where (from <= 03/02/2013) and (03/02/2013 <= to)

That is, the first one uses variables that do not require delimiters. In the second one, you have an expression of integer constants which has "constant folding" applied. Integer 3 divided by 2 divided by 2013 = 0 = 01 Jan 1900 when changed to a date

You'd need this:

set @sqlstring = 'select count(*) from table1
    where (from <= ''' + @date + ''') and (''' + @date + ''' <= to)' 

Note that you should use yyyymmdd or ISO 8601 dates for consistency and clarity

• How to prevent out-of-range datetime value error?