Determine whether numeric is a whole value 1

[madlarry]

Hi,

I'm trying to determine whether the value contained in a numeric field is a whole value or not. I.e. 12.000000 is a whole value, whereas 654.1234000 is not.

I've tried various things but I can't seem to work it out.

I thought that the following function would return 0 if there was no fraction and 1 if there was, but it always returns 0.

create function dbo.iswhole (@n numeric)
returns numeric
begin
return (@n % 1)
end
go

There must be an easy way to do this! I've searched SQL Server and Google, but to no avail.

Any help appreciated

 

[bborissov]

[COLOR=blue]create[/color] [COLOR=#FF00FF]function[/color] dbo.iswhole (@n [COLOR=blue]numeric[/color](10,2))
returns [COLOR=blue]bit[/color]
[COLOR=blue]begin[/color]
    [COLOR=blue]return[/color] [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] @n = [COLOR=#FF00FF]CAST[/color](@n [COLOR=blue]as[/color] [COLOR=blue]int[/color]) [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]
[COLOR=blue]end[/color]


[COLOR=blue]SELECT[/color] dbo.iswhole(1),
       dbo.iswhole(1.2)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Better:

[COLOR=blue]ALTER[/color] [COLOR=#FF00FF]function[/color] dbo.iswhole (@n [COLOR=blue]float[/color])
returns [COLOR=blue]bit[/color]
[COLOR=blue]begin[/color]
    [COLOR=blue]return[/color] [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] @n = [COLOR=#FF00FF]CAST[/color](@n [COLOR=blue]as[/color] [COLOR=blue]int[/color]) [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]
[COLOR=blue]end[/color]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[madlarry]

Brilliant!

Many thanks bborissov!