Datediff GETDATE Add

In this type of code, AND Orders.ShipDate >= DATEADD(Day, Datediff(Day,0, GetDate() -6), 0) It supposed to pull records with the date 6 days ago, until today. How can I make it

Solution 1:

It's not a date span.

The condition you have there is really only one condition: greater than. The right side of the greater than is 6 days ago, so your condition matches any date that is later than the date six days ago. In other words, it doesn't stop at Today; it includes tomorrow, next week, and next year, too.

AND ( Orders.ShipDate >= DATEADD(Day, Datediff(Day,0, GetDate() -7), 0)
      AND Orders.ShipDate < DATEADD(Day, Datediff(Day,0, GetDate()), 0) )

That's what you really want. It matches dates which are later than midnight of the day 7 days ago, and dates which are before midnight today (which is any time yesterday).

Solution 2:

The "end of the date span" isn't in your query.

Change your code to:

AND (Orders.ShipDate BETWEEN DATEADD(Day, -1, GetDate()) AND DATEADD(Day, -7, GetDate()))

Solution 3:

This should work too and it eliminates the needless addition of 0 days:

select *,DATEDIFF(Day, orders.ShipDate, GETDATE()) AS DAYS_SINCE_TODAY
from Orders 
where DATEDIFF(Day, orders.ShipDate, GETDATE()) >= 1 AND --This many days since today
      DATEDIFF(Day, orders.ShipDate, GETDATE()) <= 7     --Going back this many days

Solution 4:

I like the BETWEEN function. This may be a different flavor of SQL than you are using, but this is what I use.

BETWEEN dateadd(second, 0, dateadd(dd,datediff(dd,7,getdate()),0)) AND dateadd(second,-1, dateadd(dd,datediff(dd,0,getdate()),0))