"Mav" <M**@discussions.microsoft.com> wrote in message
news:28A9A0C5-3EC9-48C1-88ED-6BF889E51970@microsoft.com...
> When passing a fractional/decimal value like for example 1.5, the
> following
> UDF (User defined function) truncates the 1.5 to 1.  Why?
> ALTER FUNCTION dbo.OTimCalcChk  (@X numeric(4,2))
> RETURNS numeric(4,2)
> AS
> BEGIN
> RETURN @X
> END
> I'm using this UDF to test SQL 2000's method of operation.  I ended up
> with
> because I was getting unexpected results from a more complicated UDF.  The
> joke of it is that if you SET @X = 1.5, then it returns the correct
> result.
> Pls help me!
> --
> Mav

>When passing a fractional/decimal value like for example 1.5, the following
>UDF (User defined function) truncates the 1.5 to 1.  Why?
>ALTER FUNCTION dbo.OTimCalcChk  (@X numeric(4,2))
>RETURNS numeric(4,2)
>AS
>BEGIN
>RETURN @X
>END
>I'm using this UDF to test SQL 2000's method of operation.  I ended up with
>because I was getting unexpected results from a more complicated UDF.  The
>joke of it is that if you SET @X = 1.5, then it returns the correct result. 
>Pls help me!
> 
>

"Steve Kass" wrote:

> Mav,
>
> It sounds to me like this function is being called from a
> computer whose regional settings are set for a decimal
> separator other than period (.), probably a comma (,).
> One solution might be to pass the function a string
> (in other words, pass '1.5' instead of 1.5), so the
> conversion is done by SQL Server. If all else fails,
> you may have to change the regional settings, but how
> to proceed depends on where and how this function
> is called.
>
> Steve Kass
> Drew University
> www.stevekass.com
>
> Mav wrote:
>
> >When passing a fractional/decimal value like for example 1.5, the following
> >UDF (User defined function) truncates the 1.5 to 1.  Why?
> >ALTER FUNCTION dbo.OTimCalcChk  (@X numeric(4,2))
> >RETURNS numeric(4,2)
> >AS
> >BEGIN
> >RETURN @X
> >END
> >I'm using this UDF to test SQL 2000's method of operation.  I ended up with
> >because I was getting unexpected results from a more complicated UDF.  The
> >joke of it is that if you SET @X = 1.5, then it returns the correct result. 
> >Pls help me!
> > 
> >
>

>a. I have checked my regional settings: English South Africa.  The decimal
>symbol is a period, so it’s not that.
>b. I’m developing from within Access 2003 as I’m creating an .adp project.
>c. I tested it a bit more and got the following results:
>If I call the function from within a view(query), it gives the correct
>result, therefore your suggestion of ‘select dbo.OTimCalcChk(1.5)’ works. 
>However, the interesting thing is that when I run the function from Access
>2003’s function designer, it prompts me for input, I enter the 1.5, and then
>it truncates the input.  I can now see that there is a flaw in using that
>route to test the UDF.  I guess Access 2003 does some implicit conversion
>before passing it to SQL Server.
>Thanks!
>
>PS: Sorry for the late reply, I think the time shift means I sleep when
>you’re awake.
>
> 
>