Why do I get different results with this SQL Date Part and date parameters

Question

When I run this where clause on my table I get 2 different results and to me its seems like I should get the same number of records back.

The one I'm using just a static date to test and the other should also retrieve the same results where I'm trying to get last month results

I idea the query is a report that will automatic load the previous months records.

WHERE        
    (OrderReceiptedDate >= '2015-03-01') 
    AND (OrderReceiptedDate <= '2015-03-31')

WHERE
    (DATEPART(mm, OrderReceiptedDate) = DATEPART(mm, DATEADD(mm, - 1, GETDATE()))) 
    AND 
    (DATEPART(yy, OrderReceiptedDate) = DATEPART(yy, DATEADD(mm, - 1, GETDATE())))

you should use between two dates – Ameya Deshpande Apr 02 '15 at 10:36 — Ameya Deshpande, Apr 02 '15 at 10:36
Which dbms? (You're using product specific functionality.) – jarlh Apr 02 '15 at 10:39 — jarlh, Apr 02 '15 at 10:39

score 1 · Accepted Answer · answered Apr 02 '15 at 10:39

These are the two statements

WHERE (OrderReceiptedDate >= '2015-03-01' AND
       OrderReceiptedDate <= '2015-03-31'
      )

WHERE (DATEPART(month, OrderReceiptedDate) = DATEPART(month, DATEADD(month, - 1, GETDATE()))) AND
      (DATEPART(year, OrderReceiptedDate) = DATEPART(year, DATEADD(month, - 1, GETDATE())))

Given that today is April 2015, you are expecting that both of these get all dates for March. And, they would, if your dates had no time components. The problem is that almost any datetime on March 31st is not going to match the first condition. The one exception is exactly at midnight: 2015-03-01 00:00:00.000.

The first is better written as:

WHERE (OrderReceiptedDate >= '2015-03-01' AND
       OrderReceiptedDate < '2015-04-01'
      )

A better way to write "get me last months date" is something like:

WHERE OrderReceiptedDate >= dateadd(month, -1, cast(getdate() - day(getdate()) + 1 as date)) and
      OrderReceiptedDate < cast(getdate() - day(getdate()) + 1 as date)

This does all the calculations on getdate() so the query could still take advantage of an index on OrderReceiptDate.

Why do I get different results with this SQL Date Part and date parameters

1 Answers1