Link to home
Start Free TrialLog in
Avatar of jsctechy
jsctechyFlag for United States of America

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
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi, try this

ROUND(myfield,0)
Avatar of Aneesh
select cast(urColumn as int )
if your field is not numeric...

ROUND(CAST(myfield AS float) ,0)
Avatar of jsctechy

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.
ASKER CERTIFIED SOLUTION
Avatar of jsctechy
jsctechy
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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


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