SQL Server Datepart between, for next 'x' days

Question

I am trying to work out the best way to see what orders are due in the next 5 days

My code works if I put a -10 instead of +10 so I'm a bit confused as to what I've got wrong

EG, my code for the past 10 days works fine

select  
    PurchaseOrder, OrderDueDate
from 
    PorMasterHdr
where 
    OrderDueDate between DATEADD(day, datediff(DAY, 0, GETDATE()) -10, 0)
                     and DATEADD(day, datediff(day, 0, GETDATE()),  0) 
order by 
    OrderDueDate desc

But for the next 10 days, returns 0 rows - there is definitely items due in next 10 days

select  
    PurchaseOrder, OrderDueDate 
from  
    PorMasterHdr
where 
    OrderDueDate between DATEADD(day, datediff(DAY, 0, GETDATE()) +10, 0)
                     and DATEADD(day, datediff(day, 0, GETDATE()),  0) 
order by 
    OrderDueDate desc

Any help would be appreciated.

Answer 1

Least date should come first, ie "between '2014-11-01' and '2014-11-10' " is correct and "between '2014-11-10' and '2014-11-01 " is wrong

So your query would be :

select 
    PurchaseOrder, OrderDueDate 
from 
    PorMasterHdr 
where 
    OrderDueDate between dateadd(day, datediff(day, 0, getdate()), 0) 
                     and dateadd(day, datediff(DAY, 0, GETDATE()) +10, 0)
order by 
    OrderDueDate desc

Answer 2

You should switch between the begin/end expressions.

The code should look like:

select  PurchaseOrder, OrderDueDate 
from PorMasterHdr
where OrderDueDate between
DATEADD (day,datediff(DAY,0,GETDATE()), 0)
AND
dateadd(day,datediff(day, 0 ,getdate()) +10,  0) 
order by OrderDueDate desc