Two BETWEEN conditions in same query

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

Transact-SQL (2000)

Two BETWEEN conditions in same query

Author

Topic

Deltaur
Starting Member

29 Posts

Posted - 2009-08-06 : 06:46:02

Hi,

I dont quite understand what is wrong with this simple query.

I have query like this :

SELECT ORDER, DATE, AMOUNT, DESCRIPTION
FROM ORDERS
WHERE RIGHT(ORDER,6)=@ORDERNUM
AND (CAST (LEFT(ORDER,4) AS INT) BETWEEN 3000 AND 3999)

..ok, this works but when i put another BETWEEN condition at the end the query will not return anything

SELECT ORDER, DATE, AMOUNT, DESCRIPTION
FROM ORDERS
WHERE RIGHT(ORDER,6)=@ORDERNUM
AND (CAST (LEFT(ORDER,4) AS INT) BETWEEN 3000 AND 3999)
AND (CAST (LEFT(ORDER,4) AS INT) BETWEEN 7000 AND 7999)

the reason i CAST as int the ORDER is because the datatype is nvarchar and contains 1234 123456 (Account number, Order number) and also i cant change datatypes because it is what it is and i just have to find a way to return that range.

Could someone help and explain what i am doing wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 06:51:03

AND (CAST (LEFT(ORDER,4) AS INT) BETWEEN 3000 AND 3999)
AND (CAST (LEFT(ORDER,4) AS INT) BETWEEN 7000 AND 7999)

The two filters can never be true at any given time.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 06:52:42

[code]SELECT ORDER,
DATE,
AMOUNT,
DESCRIPTION
FROM ORDERS
WHERE RIGHT(ORDER, 6) = @ORDERNUM
AND (ORDER BETWEEN '3000' AND '3999' OR ORDER BETWEEN '7000' AND '7999')[/code]

N 56°04'39.26"
E 12°55'05.63"

Deltaur
Starting Member

29 Posts

Posted - 2009-08-06 : 07:22:20

Awesome!

Answer was closer than i expected. Thank you very much!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 07:46:36

Ei saa peitää!