views:

878

answers:

5

Hello,

I have an order header table called "OrderH". In this table there is a column called "OrderDate". I am trying to retrieve the orders with a date within a certain range. I thought that I could accomplish this with the "between" keyword but I am not having any luck. This is this SQL I have been fidgiting with:

select 
    * 
from
    OrderH h
where
        h.OrderDate between '2009-06-16' and '2009-06-01'
order by
    h.OrderDate desc

What am I doing wrong?

+4  A: 

the smaller date has to be first

between  '2009-06-01' and '2009-06-16'

instead of

between '2009-06-16' and '2009-06-01'

Also be careful when using between because you will get the midnight value from the larger date and nothing else

Take a look at How Does Between Work With Dates In SQL Server?

SQLMenace
Wow. I need more coffee. I can't believe I overlooked something so simple.
Dave Fan
A: 
select 
    * 
from
    OrderH h
where
        h.OrderDate between '2009-06-01' and '2009-06-16'
order by
    h.OrderDate desc
TheTXI
+1  A: 

Query does not work because in your example first date is bigger than second date. Swap the dates. First must be less than equal to second date.

zendar
+1  A: 

It's hard to find dates that end before they begin. Change the min and max...

h.OrderDate between '2009-06-01' and '2009-06-16'
PatrikAkerstrand
A: 

In MS-SQL Server events happening after 2009-06-16 at midnight will not be included.

jms