varchars and int

[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

 

[Douglas J. Steele]

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

 

[Sylvain Lafontaine]

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.

 

[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

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]

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

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

 

[peggyflying]

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

 

[ASS66767767667]

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