converting varchar to smallmoney

Hi
My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.
The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)
As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.
What do i have to do to convert the value as it is entered by the user?
Thanks in advance
t.
Statement:
INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()
DDL for concerned database:
CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO
Why are you converting to numeric when the data type on the table is
smallmoney? I suppose that it would properly if you declare the precision
and scale, but you aren't doing that so the insert fails.
It would be easier (and more correct and less confusing) to perform a
CONVERT(smallmoney,x) within your insert
where x is the value of the data that you are trying to insert.
Are you not using stored procedures to insert the data?
Keith Kratochvil
<thomas@.williams-mail.ch> wrote in message
news:1160570224.519187.73680@.e3g2000cwe.googlegrou ps.com...
> Hi
> My ticket engine stores values in varchar. The sql db-field that
> corresponds was created as smallmoney.
> The below statement works for conversion of "leavedays" if the given
> value is entered without any decimal places (E.G. 4)
> As soon as a user enters a value that includes decimal places (E.G.
> 4.5) the conversion will not work. In this case the value 4.5 is
> rounded to 5.
> What do i have to do to convert the value as it is entered by the user?
> Thanks in advance
>
> t.
>
> Statement:
> INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
> remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
> {1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
> {4}),{5}, getdate()
> DDL for concerned database:
> CREATE TABLE [dbo].[leavereq] (
> [mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [startdate] datetime NULL,
> [enddate] datetime NULL,
> [leavedays] smallmoney NULL,
> [remainingdays] smallmoney NULL,
> [approvedon] datetime NULL,
> [approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> ON [PRIMARY]
> GO
>