Arithmetic error

When I run a view, I am getting an SQL (ODBC) error "Arithmetic overflow
error converting real to data type numeric." Below is the line of code in
the view that I think is causing the error (dbo.WorkerTimesheets.PayRate is
a real data type and has a value of 730). Can anyone help me fix it?
Thanks.
CONVERT(varchar(6), CONVERT(decimal(4, 2), dbo.WorkerTimesheets.PayRate)) +
'/' + dbo.PayRateCodes.RateName AS txtPayRate
DavidOf course it overflows. You reserve 4 places for the entire numeric, 2 of
which are decimal places.
730 already takes up 3 places. Add 2 for decimals to that and you get 5.
Give it more room. :) I'd suggest using decimal(12, 2). If this is used to
store wages or salaries, 8 places is more than enough. :)
Spend more time reading BOL.
ML|||Geez! When I said 8 I meant 10. Yes, it's late...|||Do not use CONVERT() when you have CAST(). Never use FLOAT or REAL
unless you know what you are doing. There are all kinds rounding
errors and they display with the 'E' format.
Why are you converting numerics to strings? The first rule of a tiered
architecture is that you never do formatting in the database.
Re-do your pay rates with DECIMAL() data type; you do not bill to 16
decimal places, do you? . Read up on floating point numbers.|||You are all correct. The "overflow" was my brain. When I look at it
now, it makes sense.
David
*** Sent via Developersdex http://www.examnotes.net ***