CODEWITHSUNDEEP
sqlsql-serversql-server-2008
MCS
MCS

Reputation: 22551

sp_executesql returning different results than direct query

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

Upvotes: 4

Views: 1029

Answers (3)

Ovidiu
Ovidiu

Reputation: 1407

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

Upvotes: 4

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

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

Upvotes: 6

M.Ali
M.Ali

Reputation: 69554

Try this ...

DECLARE @p__linq__0_R datetime2(7) = '2013-11-01T04:00:00'
DECLARE @p__linq__1_R datetime2(7) = '2013-11-30T05:00:00'

DECLARE @sql NVARCHAR(MAX);

SET @sql =  N'select top 3 OrderDate
      from Orders
      where OrderDate >= @p__linq__0_s
      and OrderDate <= @p__linq__1_s
      order by OrderDate'

exec sp_executesql @sql,
    N'@p__linq__0_s datetime2(7),@p__linq__1_s datetime2(7)',
    @p__linq__0_s =@p__linq__0_R,
    @p__linq__1_s=@p__linq__1_R

Edit
If your column is just Datetime and not Datetime2 then sql server is doing an implicit conversion on your column with Datetime datatype since Datetime2 is a higher precedence datatype then datetime. to compare two values Sql server expectes values to be in same datatype. Maybe this is what causing issues. a way around will be you can CAST the datetime column to datetime2 when comparing values.

Upvotes: 0

Related Questions