Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
my table contain below data
VB
 BARCODE
2014-1024
2014-1025
2014-1026
2014-1027
2014-1028
2014-1030
2014-1031
2014-1029


I want to dispaly 2014-1026 to 2014-1030 data
Posted
Updated 12-Feb-15 1:19am
v2
Comments
CHill60 12-Feb-15 7:21am    
What have you tried?
Member 10229796 12-Feb-15 7:27am    
select ES_LIBRARY_ID as ES_BAR_CODE from es_library_user where ES_LIBRARY_ID IS NOT NULL and ES_LIBRARY_ID between '2014-100' and '2014-1029'
CHill60 12-Feb-15 7:34am    
And what is wrong with that - except that you need to have between '2014-1026' and '2014-1030'
Member 10229796 12-Feb-15 7:42am    
if give between '2013-100' and '2013-102'
it display like
2014-1000
2014-1010
2014-1011
2013-1012
2013-1013
2014-1016
2014-1017
2014-1018
2014-1019
Tomas Takac 12-Feb-15 7:35am    
In your query you use different values than in the question. But generally between should work with 2014-1026 and 2014-1030. Not sure about '2014-100' and '2014-1029'.

Your select works.
If I create a new table:
MyColumn
2014-1024 
2014-1025 
2014-1026 
2014-1027 
2014-1028 
2014-1030 
2014-1031 
2014-1029 
And use your select:
SQL
SELECT * FROM Table_1
WHERE MyColumn IS NOT NULL AND MyColumn BETWEEN '2014-1026' AND '2014-1030'

Then I get what you are asking for:
C#
MyColumn
2014-1026 
2014-1027 
2014-1028 
2014-1030 
2014-1029 

If you want to exclude '2014-1029' because the earlier values include greater values, then you will probably need additional info, as SQL doesn't normally work on row numbers, and (unless you specify a sort order explicitly with an ORDER BY clause) can return rows in any order it finds convenient: so you can;t guarantee that the order of rows you show in your example data will be preserved unless there is a different column on which to order.
 
Share this answer
 
Comments
/\jmot 12-Feb-15 8:22am    
+5 for explanation. :)
SQL
select
    * 
from BarCodeTable 
where
  Convert(int,Replace((barcode,'-',''))>=20141026
  and Convert(int,Replace((barcode,'-',''))<=20141030
 
Share this answer
 
Comments
/\jmot 12-Feb-15 8:24am    
this is not gonna work??
where is the logic??
you just cross everything..
Shweta N Mishra 12-Feb-15 8:40am    
What i crossed ?

did you tried the code

check this

Create table #Barcode(Barcode Varchar(100))

insert #Barcode select '2014-1024'
insert #Barcode select '2014-1025'
insert #Barcode select '2014-1026'
insert #Barcode select '2014-1027'
insert #Barcode select '2014-1028'
insert #Barcode select '2014-1030'
insert #Barcode select '2014-1031'
insert #Barcode select '2014-1029'

select
*
from #Barcode
where
Convert(int,Replace(barcode,'-',''))>=20141026
and Convert(int,Replace(barcode,'-',''))<=20141030

Try..
SQL
select * from Table_Name where (SUBSTRING(barcode,1,4)>=2014 and SUBSTRING(barcode,6,4)<=1026) or
(SUBSTRING(barcode,1,4)>=2014 and SUBSTRING(barcode,6,4)<=1030)
 
Share this answer
 
Comments
/\jmot 12-Feb-15 8:20am    
hahaha..
somebody just downvote my answer??
will you please say me, why?? :)
CHill60 12-Feb-15 8:26am    
Couple of thoughts as to why ... it's slightly over-engineered - BETWEEN would be a better route (see OriginalGriff's answer). And you are comparing a varchar from the substring with a numeric without casting - whereas it probably works it's not good to rely on implicit conversions. Finally - should that "or" be an "and"?
/\jmot 12-Feb-15 10:46am    
thanks,for your answer .
but i think he's just want to compare the two part of the string value separated by a '-' sign,that's why i post it.
YES, you are right i forgot to cast those two string to Integer value.

btw, thank you so much. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900