Field gets changed to Money type

Question

Field is defaulted to UA=F but in SQL the field is changing to money.

How can I make this field a varchar field?  The below is what I have but not working

, Field = case when dedcode<>'ABC' then cast(NULL as varchar(9))

when dedcode = 'ABC' then

case when cast(Salary as varchar) >= '120000' then '120000'

when cast(Salary as varchar) between '20000' and '120,000' then CEILING(Salary/1000)*1000 --

when cast(Salary as varchar) <= '20000' then '20000' end --

end

Outcome is always 120000.00 not what the statement states.

Answer 1

Try:

, Field = case when dedcode<>'ABC' then cast(NULL as varchar(9))

when dedcode = 'ABC' then

case when Salary >= 120000 then '120000'

when Salary between 20000 and 120000 


then CONVERT(VARCHAR(9),CEILING(Salary/1000)*1000) -- the problem is here

when Salary <= 20000 then '20000' end 

end

Also, no need to convert salary to varchar for comparison.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

... THEN CONVERT(varchar(100), CEILING(Salary/1000) * 1000)

Too late :-(

Answer 3

Still coming up as money XXXXX.XX

Not making it a flat number with no .00.

Answer 4

You can also do

then CONVERT(VARCHAR(9),ROUND(CEILING(Salary/1000)*1000,0)) -- the problem is here

to make sure to remove extra .00

Or 

then CONVERT(VARCHAR(9),CONVERT(DECIMAL(9,0),CEILING(Salary/1000))*1000) -- the problem is here

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog