CODEWITHSUNDEEP
t-sqlsyntaxbetween
Dave Fan
Dave Fan

Reputation: 37

SQL - Query by Date Range

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?

Upvotes: 1

Views: 20818

Answers (5)

jason saldo
jason saldo

Reputation: 9980

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

Upvotes: 0

PatrikAkerstrand
PatrikAkerstrand

Reputation: 45731

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'

Upvotes: 2

zendar
zendar

Reputation: 13582

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.

Upvotes: 1

TheTXI
TheTXI

Reputation: 37905

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

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135181

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?

Upvotes: 8

Related Questions