sp_executesql returning different results than direct query

Question

The following two queries are returning different results. I understand the difference has to do with the way the time portions of the dates are being handled, but why is it working this way?

// QUERY ONE
select top 3 OrderDate
from Orders
where OrderDate >= '2013-11-01 04:00'
and OrderDate <= '2013-11-30 05:00'
order by OrderDate

// RESULTS
// 2013-11-01
// 2013-11-01
// 2013-11-01

// QUERY TWO
exec sp_executesql
    N'select top 3 OrderDate
      from Orders
      where OrderDate >= @p__linq__0
      and OrderDate <= @p__linq__1
      order by OrderDate',
    N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',
    @p__linq__0='2013-11-01T04:00:00',
    @p__linq__1='2013-11-30T05:00:00'

// RESULTS
// 2013-11-02
// 2013-11-02
// 2013-11-02

UPDATE

If I change the types of the parameters passed to sp_executesql to 'date' instead of 'datetime', the results are the same.

// QUERY THREE
exec sp_executesql
N'select top 3 OrderDate
  from Orders
  where OrderDate >= @p__linq__0
  and OrderDate <= @p__linq__1
  order by OrderDate',
N'@p__linq__0 date,@p__linq__1 date',
@p__linq__0='2013-11-01T04:00:00',
@p__linq__1='2013-11-30T05:00:00'

// RESULTS
// 2013-11-01
// 2013-11-01
// 2013-11-01

Answer 1

Data type precedence is taking the data in your table, which starts as a date, and compares it as a datetime2(7). So your dynamic SQL version is actually running this:

WHERE column_as_datetime2 >= @parameter_as_datetime2

So, since 2013-11-01 00:00:00.0000000 is not greater than or equal to 2013-11-01 04:00:00.0000000, the rows from November 1st are left out.

The most practical solution is to use DATE parameters (preferred, since the parameters should match the underlying data type, after all), and/or stop passing time values along with them. Try these:

USE tempdb;
GO

CREATE TABLE dbo.Orders(OrderDate DATE);

INSERT dbo.Orders VALUES('2013-11-01'),('2013-11-01'),('2013-11-01'),
  ('2013-11-02'),('2013-11-02'),('2013-11-02');

exec sp_executesql N'select top 3 OrderDate
      from Orders
      where OrderDate >= @p__linq__0
      and OrderDate <= @p__linq__1
      order by OrderDate;
select top 3 OrderDate
      from Orders
      where OrderDate >= @p2
      and OrderDate <= @p3
      order by OrderDate;
select top 3 OrderDate
      from Orders
      where OrderDate >= @p4
      and OrderDate <= @p5
      order by OrderDate;',
    N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),
      @p2 datetime2(7),@p3 datetime2(7),@p4 date,@p5 date',
    @p__linq__0='2013-11-01T04:00:00',
    @p__linq__1='2013-11-30T05:00:00',
    @p2='2013-11-01T00:00:00', -- note no time
    @p3='2013-11-30T00:00:00', -- note no time
    @p4='2013-11-01',
    @p5='2013-11-30';

Results:

OrderDate
----------
2013-11-02
2013-11-02
2013-11-02

OrderDate
----------
2013-11-01
2013-11-01
2013-11-01

OrderDate
----------
2013-11-01
2013-11-01
2013-11-01

Answer 2

I bet the column OrderDate is of type date, not datetime. So when you do this

where OrderDate >= '2013-11-01 04:00'

it converts '2013-11-01 04:00' to date, not datetime, and so it loses the time information. Therefore, the condition in the first query is interpreted as '2013-11-01 00:00:00' >= '2013-11-01 00:00:00'. Which is true.

In the second query, the SP receives a parameter of type datetime, which has the time information. The condition there is interpreted as '2013-11-01 00:00:00' >= '2013-11-01 04:00:00' which is false.

If you want the same behavior in the first query, use a datetime variable instead of a string.

declare @d1 datetime
declare @d2 datetime
set @d1 = '2013-11-01 04:00'
set @d2 = '2013-11-30 05:00'

select top 3 OrderDate
from Orders
where OrderDate >= @d1
and OrderDate <= @d2
order by OrderDate