weisietan (11/18/2011)
I try using the sql server 2000 to retrieve date using Betweenthe first data will counted but the last will not counted.
Example:
Name between 'A%' and 'C%'
just will get the A & B data only..
so ...the answer i think no so correct ...
Your BETWEEN clause translates to:
WHERE Name >= 'A%' AND NAME <= 'C%'
This means that a name that is just 'A' will not match (it sorts before 'A%' in any collation I know). A name that is just 'C' will match, as will any name that starts with a C and has a second character that sorts before the % sign in your collation, optionally followed by more characters. Here's a repro:
CREATE TABLE Test
(Name varchar(20) COLLATE Latin1_General_CI_AI);
go
INSERT INTO Test (Name) VALUES ('A')
INSERT INTO Test (Name) VALUES ('B')
INSERT INTO Test (Name) VALUES ('C')
INSERT INTO Test (Name) VALUES ('A$')
INSERT INTO Test (Name) VALUES ('B$')
INSERT INTO Test (Name) VALUES ('C$')
INSERT INTO Test (Name) VALUES ('A%')
INSERT INTO Test (Name) VALUES ('B%')
INSERT INTO Test (Name) VALUES ('C%')
INSERT INTO Test (Name) VALUES ('Aa')
INSERT INTO Test (Name) VALUES ('Bb')
INSERT INTO Test (Name) VALUES ('Cc')
INSERT INTO Test (Name) VALUES ('A$Z')
INSERT INTO Test (Name) VALUES ('B$Z')
INSERT INTO Test (Name) VALUES ('C$Z')
SELECT Name FROM Test
WHERE Name BETWEEN 'A%' AND 'C%'
ORDER BY Name;
go
DROP TABLE Test;
The % sign has a special meaning is LIKE searches only.