jsctechy

asked on

# how can i get the number or numbers right before the decimal period in SQl

hi,

I have values such as:

8.00

12.50

9.50

9.00

and i need to get the whole numbers before the decimal. HOw can i get this? i know i can used the function CHARINDEX but it does not seems to work for me.

can you experts show me how can i do this?

I'm using SQL 2000

thanks

I have values such as:

8.00

12.50

9.50

9.00

and i need to get the whole numbers before the decimal. HOw can i get this? i know i can used the function CHARINDEX but it does not seems to work for me.

can you experts show me how can i do this?

I'm using SQL 2000

thanks

select cast(urColumn as int )

if your field is not numeric...

ROUND(CAST(myfield AS float) ,0)

ROUND(CAST(myfield AS float) ,0)

ASKER

I forgot to mention that the number can not be round off. what im trying to do is to get the numbers where the value after the decimal are 00 and display as a whole number. and the ones with decimal values then show the decimal.

Something like this:

SELECT CASE WHEN CONVERT(VARCHAR, Mo11) like '%.00' then 'Show Whole number else 'Display the number with decimal' end as mycolunm from mytable.

also, is there a way i can test for a fractional number?

Thanks, and i'm sorry for the confusion.

Something like this:

SELECT CASE WHEN CONVERT(VARCHAR, Mo11) like '%.00' then 'Show Whole number else 'Display the number with decimal' end as mycolunm from mytable.

also, is there a way i can test for a fractional number?

Thanks, and i'm sorry for the confusion.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Your question seems to deal with the display rather than data extraction manipulation? Are you presenting this information in some sort of web page application? Once you have the data it is easy to alter for formating.

Using http://www.devx.com/tips/Tip/15633 as an example for use of case:

select

case when myfield=cast(myfield as int) then cast (myfield as int)

else cast (myfield as float)

end

Using http://www.devx.com/tips/Tip/15633 as an example for use of case:

select

case when myfield=cast(myfield as int) then cast (myfield as int)

else cast (myfield as float)

end

jsctechy, your way is the way I would do it.

ROUND(myfield,0)