Dynamic SQL query returning unexpected results when comparing dates

Question

I am building a dynamic query as a stored procedure in SQL Server 2008 and I'm getting some unexpected results when I compare a date (supplied as a parameter) and a datetime (stored in the database). I searched around for some ways to compare the two without taking the time portion into consideration and found this:

DATEDIFF(day, @d, v.ScheduledDate) = 0

What I'm doing with this is trying to find records where the supplied date parameter and the stored ScheduledDate are the same (in terms of days, e.g. 02/05/2011 and 02/05/2011 11:26:19.157). This is query that I wrote to do this:

SET @sql = 'SELECT e.Id, e.FirstName, e.LastName, v.ScheduledDate
            FROM Employee e, Visit v
            WHERE 1=1'
-- Several IF IS NOT NULL statements here

IF @d IS NOT NULL
BEGIN
  SET @sql = @sql + ' AND DATEDIFF(day, ''' + @d + ''',' + 'v.ScheduledDate) = 0
  AND v.EmpId = e.Id '
END
EXEC (@sql)

I would expect that this query would result in the all of the ScheduledDates for any employee who has a visit scheduled for that day. In other words, if I have two employees with Ids 5 and 7, and there exists in the Visit table two ScheduleDate entries on 02/05/2011 for employees with Ids 5 and 7, I would expect to get both those employees back when I run this query. It seems, however, that when I run it, I only get one row back. (As a side note, the two ScheduledDate entries I'm working with are on the same day, but about 3 hours apart from each other. I would think that the DATEDIFF function would account for this, since a few hours is certainly within the time frame of a day.) If I change the = in the query to a >= 0 or a <= 0, I get more rows as expected, but oddly enough still only get the single entry for that specific date. There are other records in the table where the same Employee has multiple visits on different dates, and those are returned accordingly when I use >= 0 or <= 0. For example, Employee with Id 41 has 3 visits on 10-29-2011, 11-24-2011, and 12-28-2011 and all 3 of those are returned when I change DATEDIFF to >= 0. I'm still confused as to why I'm only getting back the single record when two different Employees have a visit scheduled on the same date. Could anyone provide some insight as to where my logic is going wrong here? Note that when I'm testing this, I'm only supplying the ScheduledDate parameter. All of the other IF IS NOT NULL statements just fall through since all other params are inserted as NULL.

Answer 1

This is in depth, but it's very informative: http://www.sommarskog.se/dyn-search.html

SET @sql = N'SELECT e.Id, e.FirstName, e.LastName, v.ScheduledDate
             FROM Employee e, Visit v
             WHERE v.EmpId = e.Id'

-- Several IF IS NOT NULL statements here
IF @d IS NOT NULL
  SET @sql = @sql + N' AND (v.ScheduledDate >= @date AND v.ScheduledDate < @date + 1)'

-- This stays the same, EVEN if the parameter is NULL and not used
-- This ensures execution plan re-use is available
SET @param_definition = '@date DATETIME,    -- Or whatever type v.ScheduledDate is
                         @smeg INT,
                         @head WHATEVER'

SP_EXECUTESQL
    @sql,
    @param_definition,
    @date = CAST(@d AS DATE),
    @smeg = 0,
    @head = NULL

Answer 2

I think the best practice is to remove the time portion of the date do that like so

DECLARE @dt DATETIME
SELECT @dt = GETDATE()
SELECT CAST(FLOOR(CAST(@dt AS FLOAT)) AS DATETIME)

then you can just use plain old = to compare

Answer 3

I just did the SAME thing today (dynamic query with that line of code). This was my line of code:

DATEDIFF(day,SchDate,GetDate()) = 0

It worked perfectly. Sorry I can't help you any further but I read that like 4 times and I can't find any mistakes in the datediff portion. I did the same thing as you did and it worked flawlessly. However, if you want to you can use something like:

(AND day(@d) = day (v.ScheduledDate)) AND (month(@d) = month(v.ScheduledDate)) AND (year(@d) = year(v.ScheduledDate))

Maybe that will work (I just tested it with random dates and it did). Ofc you need to add the +@d+ and stuff because the dynamic query doesn't have the @d variable, but you already know that =-)

Hope this helps!

Answer 4

This could be way off, but my best guess from everything you've described so far (including your comments on Dems' answer) is that the relation between Employee and Visit is not defined solely by v.EmpId = e.Id, but rather a compound key, and you're missing part of it in your join/where condition. Something like v.EmpId = e.Id AND v.CompanyID = e.CompanyID.