Search code examples
sqlsql-server-2005datediffdateadd

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?

Solution

  • 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).