how to convert to text type - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to convert to text type

Author

Topic

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2006-04-06 : 09:32:04

Hello,
I have a question:
I have one table like employee_tbl (emp_id, name, salary), I want to select data from this table like name - $salary, but it keeps getting this error:
Arithmetic overflow error converting varchar to data type numeric.

This is my query:

SELECT name + ' - ' + salary from employee_tbl

Can you show me how to convert this to make it work?
Thanks,
Jenny.

There is no stupid question.
www.single123.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-06 : 09:35:15

Why do you want to concatenate salary with name?

You need to convert salary to varchar
SELECT name + ' - ' + convert(varchar(20),salary) from employee_tbl

Madhivanan

Failing to plan is Planning to fail

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2006-04-06 : 09:55:34

It worked, but how do I include a $ format before the money and how do I minimize the decimal point to 2.
EX: Jenny Pretty - $25,192.25

I just want to display in CF pages that is similar to MS Access.

Thanks.
Jenny.

There is no stupid question.
www.single123.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-06 : 10:00:45

This is the formation issue that should be done in front end application.
Use string variable;append the data with the format you want;display that string

Madhivanan

Failing to plan is Planning to fail

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2006-04-06 : 10:28:14

Here is my cfquery and loop, I don't know where to put the string and variable. Can you help please?

<cfquery name="loadStuRate" datasource="jenny">
Select emp_id, name + ' - ' + convert(varchar(20),salary) from employee_tbl as description
from employee
</cfquery>
..........

<select name="loadStuRate" size="1">
<cfloop query="loadStuRate">
<option value=#emp_id# <cfif #salary_window.emp_id# eq #emp_id#>selected</cfif>>#description# </option>
</cfloop>
</select>

Thanks.

There is no stupid question.
www.single123.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-07 : 01:20:14

I dont know about the front end you are using. Use for or while loop to get each row and assign them to variable as I suggested. Otherwise use this though not advisible

SELECT name + ' - ' + '$'+convert(varchar(20),convert(decimal(12,2)salary)) from employee_tbl

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources