SQL - Query by Date Range

Question

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?

Solution 1

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?

Solution 2

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'

Solution 3

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.

Comments

[Milen A. Radev]

Please add more info about which DBMS is that, description of the table in question, error messages if any, etc.

[Wadih M.]

If MySQL: is your OrderDate a "date" type column or a "timestamp" column?

[Dave Fan]

Wow. I need more coffee. I can't believe I overlooked something so simple.