T-sql datediff doesn't look at the year if you pass in day for datepart?

Question

I am using datediff in a query to return the dates that are less than 7 days from the start date.

DECLARE @d1 DATE
SET @d1 = (SELECT CurrentServiceWeek FROM Technician WHERE ID = 150) 

SELECT 
    First_Name, Last_Name, Previous_Service_Date, 
    Address1, Address2, City, [State], Zip 
FROM 
    Customer 
JOIN 
    CustomerAddress ON Customer.ID = Customer_ID
WHERE 
    Technician_ID = 150 
    AND DATEDIFF(day, (@d1), (SELECT Previous_Service_Date 
                              FROM Customer 
                              WHERE Technician_ID = 150)) < 7

That datediff is also giving me more than one result. CurrentServiceWeek is equal to 3/11/2016 and is the start date so any dates 6 days out will suffice the query. But the dates that it returned as a Previous_Service_Date value are:

4/5/2015
3/6/2016

So the query should only return the 3/6/2016 date. Can someone help me out so I can see where I'm going wrong please? Is DATEDIFF ignoring the year in the date since I passed a day for the datepart parameter?

There is no need to query the base table again to get the same row you have in your main query. I can't quite get what you are trying to do here but you shouldn't need the Previous_Service_Date as part of the date calculation. — Sean Lange, Mar 27 '15 at 13:26

score 0 · Answer 1 · answered Mar 30 '15 at 13:32

Got it! In this line of code:

WHERE Technician_ID = 150)) < 7

It's going to grab any result less than 7 days...this includes NEGATIVE values so we need something like.

WHERE Technician_ID = 150)) BETWEEN 0 and 7

To get the dates that are the same day as the start date and no more than 7 days more than the start date. A silly logical error I made.

T-sql datediff doesn't look at the year if you pass in day for datepart?

1 Answers1