CODEWITHSUNDEEP
sqlsql-serversql-server-2012
B-M
B-M

Reputation: 1288

SQL between clause returns rows for values outside bounds

As the title says when I use the sql between clause I am running it against an address range from low to high and I am putting in a number that is outside of the two and getting rows returned to me. And I would like to know why.

http://www.sqlfiddle.com/#!6/49467/2

Quick hit on the query, I am using an address number of 4929 and getting rows returned to me where the address range low and high numbers are 400 and 498 respectively.

Here is the query:

SELECT 

ZipCodeLow ,
ZipCodeHigh ,
ZipExtensionLow ,
EndingEffectiveDate ,
BeginningEffectiveDate ,
AddressRangeLow ,
AddressRangeHigh ,
StreetName ,
City ,
ZipCode ,
Zip4,
Zip4High

FROM BoundTable

WHERE 

('68503' BETWEEN ZipCodeLow AND ZipCodeHigh) AND 
('4929' BETWEEN [AddressRangeLow] AND [AddressRangeHigh]) AND
([StreetName] = '32ND') AND
(GETDATE() BETWEEN [BeginningEffectiveDate] AND [EndingEffectiveDate])

Upvotes: 2

Views: 171

Answers (1)

Sonam
Sonam

Reputation: 3466

Convert 4929 to numeric first and then run the query:

  SELECT 
  ZipCodeLow ,
  ZipCodeHigh ,
  ZipExtensionLow ,
  EndingEffectiveDate ,
  BeginningEffectiveDate ,
  AddressRangeLow ,
  AddressRangeHigh ,
  StreetName ,
  City ,
  ZipCode ,
  Zip4,
  Zip4High
  FROM BoundTable

  WHERE 
  ('68503' BETWEEN ZipCodeLow AND ZipCodeHigh) AND 
  (convert(numeric,'4929') BETWEEN [AddressRangeLow] AND [AddressRangeHigh]) AND
  ([StreetName] = '32ND') AND
  (GETDATE() BETWEEN [BeginningEffectiveDate] AND [EndingEffectiveDate])

Upvotes: 2

Related Questions