Datediff GETDATE Add

Question

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?

Brian Arsuaga · Accepted Answer · 2012-02-06T23:55:22.293

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

You have an extra parenthesis in there – henryaaron Feb 06 '12 at 23:44 — henryaaron, Feb 06 '12 at 23:44

score 0 · Answer 2 · answered Apr 26 '16 at 19:36

0

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

answered Apr 26 '16 at 19:36

MarvinM

601
6
7

NotMe · Answer 3 · 2012-02-06T23:46:24.930

0

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

edited Feb 06 '12 at 23:46

answered Feb 06 '12 at 23:27

NotMe

87,343
27
171
245

@user1090389: the above is cleaner. – NotMe Feb 06 '12 at 23:46

score 0 · Answer 4 · answered Feb 07 '12 at 00:16

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

Datediff GETDATE Add

4 Answers4

Linked