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 pull records from 7 days ago until yesterday?

I know changing -6 to -7 will pull records from 7 days ago, but which variable is the end of the date span so I can change it to -1?

回答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).

回答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()))

回答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

回答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))