Query Regarding Condition 'Between '

I have a table(CNT) with record in Cnt_Number (Varchar) as 'CNT01/2006','CNT02/2006','CNT03/2006','CNT04/2006','CNT05/2006'.

When i use the following query it is returning only three instead of only four it should. How to resolve this

select Cnt_Number from CNT where Cnt_Number between 'CNT01' and 'CNT04'

It returns only

Cnt_Number

CNT01/2006

CNT02/2006

CNT03/2006

My Expected result was

CNT01/2006

CNT02/2006

CNT03/2006

CNT04/2006

Kindly anyone help me in this regards

Thanks in advance

Bubben

Hello

Thanks for your clarification and timely help
Nagul
As long as the initial part of the string you are looking for is a fixed length, you could accomplish the same thing by doing the following:

SELECT * FROM SomeTable
WHERE LEFT(Cnt_Number,5) BETWEEN 'CNT01' and 'CNT04'
testvoid
This is because

Cnt_Number 'CNT04' < 'CNT04/2006'

Change to

Cnt_Number between 'CNT01/2006' and 'CNT04/2006'

HTH
dino.net
Hi,

actually this is normal behaviour:

Create Table SomeTable

(

Cnt_Number VARCHAR(20)

)

GO

INSERT INTO SomeTable

VALUES ('CNT01/2006')

INSERT INTO SomeTable

VALUES ('CNT02/2006')

INSERT INTO SomeTable

VALUES ('CNT03/2006')

INSERT INTO SomeTable

VALUES ('CNT04/2006')

SELECT * FROM SOMETABLE

WHERE Cnt_Number BETWEEN 'CNT01' and 'CNT05'

GO

SELECT 'Upps' WHERE 'CNT04' = 'CNT04/2006'

Look in the BOL for more information Between follows the following rules:

"BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression."

But 'CNT04' is smaller than 'CNT04/2006', and 'CNT04/2006' is greater than 'CNT04' and not equal, so this one is wiped out.

HTH, jens Suessmeyer.
Query Regarding Condition 'Between '