varchars and int

  • Thread starter Thread starter Matthew
  • Start date Start date
M

Matthew

I have build a shopping cart system with MSSQL and it runs fine. However,
my client changed the project reqirements (how often have you heard that?)

They make wood parts, and want to sell them whole (as they are in the DB
now) and split in half.

My development site uses an Access .mdb file. The following worked OK:

SELECT ID + '.5' AS id
FROM tablename

However, when I tried it in MSSQL I get this error:
Syntax error converting the varchar value '.5' to a column of data type int.

I know it might be considered bad database design. However, I was trying to
save some work. My fear is I might have to redesign the order system to
reference by part number instead of the part's ID.

So, what do you folks think?

Matthew
 
What do you want it to be: numeric or text?

For numeric, I believe you just need to remove the quotes:

SELECT ID + .5 AS ID
FROM TableName

It's possible you might have to convert the integer ID to decimal first.

SELECT CAST([ID] AS decimal) + .5 AS ID
FROM TableName

or reversing the order of .5 and ID might work as well.

For text, you'll need to use the CAST function to convert the number to
text:

SELECT CAST([ID] AS varbinary(20)) + '.5' AS ID
FROM TableName
 
Convert first the ID to the varchar type:

select convert (varchar (12), id) + '.5' as id

There is also the operator CAST, which basically do the same thing as
Convert() but with a different syntax.

S. L.
 
Douglas J. Steele said:
What do you want it to be: numeric or text?

For numeric, I believe you just need to remove the quotes:

SELECT ID + .5 AS ID
FROM TableName
Click to expand...

That solved it! I am a bit embarrassed; I know better than that.
Oh well, I guess I just need to get some extra sleep tonight.

Thanks for your help.

Matthew
 
Douglas J. Steele said:
What do you want it to be: numeric or text?

For numeric, I believe you just need to remove the quotes:

SELECT ID + .5 AS ID
FROM TableName
Click to expand...

That solved it! I am a bit embarrassed; I know better than that.
Oh well, I guess I just need to get some extra sleep tonight.

Thanks for your help.

Matthew
 
Matthew said:
I have build a shopping cart system with MSSQL and it runs fine. However,
my client changed the project reqirements (how often have you heard that?)

They make wood parts, and want to sell them whole (as they are in the DB
now) and split in half.

My development site uses an Access .mdb file. The following worked OK:

SELECT ID + '.5' AS id
FROM tablename

However, when I tried it in MSSQL I get this error:
Syntax error converting the varchar value '.5' to a column of data type int.

I know it might be considered bad database design. However, I was trying to
save some work. My fear is I might have to redesign the order system to
reference by part number instead of the part's ID.

So, what do you folks think?

Matthew
Click to expand...
 
U¿ytkownik "Matthew said:
I have build a shopping cart system with MSSQL and it runs fine. However,
my client changed the project reqirements (how often have you heard that?)

They make wood parts, and want to sell them whole (as they are in the DB
now) and split in half.

My development site uses an Access .mdb file. The following worked OK:

SELECT ID + '.5' AS id
FROM tablename

However, when I tried it in MSSQL I get this error:
Syntax error converting the varchar value '.5' to a column of data type int.

I know it might be considered bad database design. However, I was trying to
save some work. My fear is I might have to redesign the order system to
reference by part number instead of the part's ID.

So, what do you folks think?

Matthew
Click to expand...
 
Back
Top