What Datatype I Have To Take For '01.10.10' In Sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi guys,

i'm little bit confuse.

In Sql Server 2008 R2.

i was creating a table for references, where i got stuck n confused for,

what data type should i take for numbers like '01.10.01' its like Reference#.

after inserting, i have to select these references between range.

So, can anyone please help me...

Thanks

Posted 25-Nov-14 6:20am

abdul subhan mohammed

Updated 26-Nov-14 3:24am

v3

Add a Solution

Comments

Tomas Takac 25-Nov-14 12:30pm

Can you give an example of the range select?

abdul subhan mohammed 25-Nov-14 12:43pm

01.10.01 to 01.28.80, 02.20.02 to 02.30.80.....12.02.01 to 12.28.80

Tomas Takac 25-Nov-14 13:01pm

If the reference is xx.yy.zz what are the min & max values for xx/yy/zz?

Shweta N Mishra 25-Nov-14 13:56pm

i have a doubt that how do you filter with this range

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tomas Takac · Answer 1 · 2014-11-25T10:32:00

I would store your reference numbers as integers in the database. For that of course a transformation is required, rather simple though. This assumes that each part of the identifier fits into a byte i.e. is between 0 and 255. Then in your code you need to do this:

C#

// convert to integer
int dbValue = ((int)xx << 16) + ((int)yy << 8) + zz;

// convert back to components
byte xx = (byte)(dbValue >> 16);
byte yy = (byte)(dbValue >> 8);
byte zz = (byte)dbValue;

if your identifier is wrapped by a class this shouldn't be tto complicated to achieve. In database you are just comparing integers and you can use between ... and ... directly.

jaket-cp · Answer 2 · 2014-11-26T05:20:00

Varchar should work, but it may depend on what the collation is, but with numbers formatted the way you have them it should be okay.
Comparisons can be done using the Between, Greater Than/Less Than and Like, see example below:

SQL

declare @r table (ref varchar(50));
insert into @r
select '01.10.01' ref union all
select '01.28.80' ref union all
select '02.20.02' ref union all
select '02.20.03' ref union all
select '02.20.50' ref union all
select '02.25.90' ref union all
select '02.30.80' ref union all
select '12.02.01' ref union all
select '12.28.80';

select * from @r where ref between '02.20.02' and '12.02.01';
--result
/*
02.20.02
02.20.03
02.20.50
02.25.90
02.30.80
12.02.01
*/
select * from @r where ref >= '12.02.01';
--result
/*
12.02.01
12.28.80
*/
select * from @r where ref like '02.20%';
--result
/*
02.20.02
02.20.03
02.20.50
*/

I am not sure how efficient it is though.
I used to have fields called SortCode similar to what you have but instead of 2 digit numbers, they where 3 digit.
Also only ever used the Like to query them, never thought of using Between and Greater Than/Less Than.

What Datatype I Have To Take For '01.10.10' In Sql

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0