Why BETWEEN Parameter gives different reults If we switch the dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Why BETWEEN Parameter gives different reults If we switch the dates

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-10 : 08:49:51

Vijay writes "Hello,

I am using Between Operator for a simple Query, I am getting different results if I switch the dates.

1)
SELECT * FROM Invoice WHERE Invoice_Date BETWEEN '04/01/2001' AND '04/01/2002'
=== Returns 56 Records ====

2)
SELECT * FROM Invoice WHERE Invoice_Date BETWEEN '04/01/2002' AND '04/01/2001'
=== Returns no Records ===

Facts:
1.DB: SQL Server 2000
2.Invoice_Date: smalldatetime

Invoice_Date Values in Database: 2002-01-21 00:00:00

Any info is highly appreciated.

Thanks"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-10 : 08:58:02

When using BETWEEN, the lower value must appear first, and the higher value second. BETWEEN is logically evaluated into:

column BETWEEN 1 AND 10 translated into column >= 1 AND column <= 10

If you reverse the values:

column BETWEEN 10 AND 1 translated into column >= 10 AND column <= 1

The latter is not logically possible. And no, this is not a bug; it is defined in the ANSI SQL standard, and the optimizer will not "correct" it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources