HELP! Data Conversion Rounding Problem

I have a somehwat odd situation. I have a field on table A defined as
float. It is being written to a varchar(15) field on table B. If the
field on A is NULL I want the field on B to be '0'. It cannot have
two decimal places. It must be a single character of zero.
If the field in table A is a number, it must be written to table B as
the same number. Here are some examples:
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.21 <-- This is what I want
35444.79 35444.79 <-- This is what I want
My problem is with the last two. Any amount > 9,999.99 rounds the
second decimal value. So in the example above I get this (which is
wrong):
A B
NULL 0
123.45 123.45
555.70 555.7
35444.21 35444.2 <-- This is wrong
35444.79 35444.8 <-- This is wrong
I've tried multiple way of using CONVERT and CAST. When I use decimal
or money I get the two decimal places for all values correct. However,
now my '0' turns into '0.00' (which is wrong). I need it to not have
the two decimal places.
Any suggestions?Separate presentation from data. SQL Server returns data values and the valu
e 0 is the same as the
value 0.00. The client application presents the binary values returned by SQ
L Server in a
human-readable form (like number with decimal points). If you didn't write t
he client app yourself,
i.e., if you use something like QA, SSMS, OSQL, ISQL, SQLCMD, Reporting Serv
ices, etc, then these
tools will present the values according to the datatype *for the column'. I.
e., a tool might assume
that the money datatype will be presented with two decimals. Or 4. IT is the
person writing the too
who makes these decision, but it applies to all values for the column. The o
nly way to not being at
the mercy of the tool vendor is to convert to strings, but that will lead to
some potentially
lengthy CASE expressions in the SELECT statement. So, my suggestion is to ha
ndle this in the client
app. All client app designed for end users has functionality for formatting
data (like for instance
a format() function).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul" <pwh777@.hotmail.com> wrote in message
news:1174935075.294994.16170@.l75g2000hse.googlegroups.com...
>I have a somehwat odd situation. I have a field on table A defined as
> float. It is being written to a varchar(15) field on table B. If the
> field on A is NULL I want the field on B to be '0'. It cannot have
> two decimal places. It must be a single character of zero.
> If the field in table A is a number, it must be written to table B as
> the same number. Here are some examples:
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.21 <-- This is what I want
> 35444.79 35444.79 <-- This is what I want
> My problem is with the last two. Any amount > 9,999.99 rounds the
> second decimal value. So in the example above I get this (which is
> wrong):
> A B
> NULL 0
> 123.45 123.45
> 555.70 555.7
> 35444.21 35444.2 <-- This is wrong
> 35444.79 35444.8 <-- This is wrong
> I've tried multiple way of using CONVERT and CAST. When I use decimal
> or money I get the two decimal places for all values correct. However,
> now my '0' turns into '0.00' (which is wrong). I need it to not have
> the two decimal places.
> Any suggestions?
>