Pick Specific Data Position

[Zurich98]

I have a float data type field that stored sales amount. What I'm trying to accomplish is to pull the specific digits from this field. For example

Amount_
5565295.24

I want to pull 952 two digit to the left of the decimal (95) and one digit to the right of the decimal (2). I want to use the charindex to locate the decimal position and substring function to pull it. But the charindex does not give me the correct position. Here is what I have

select *,charindex('.',Amount_) as position
from Sales_

result:

Amount_ position
5565295.24 2

I'm not sure if this the right approach. Your help/input is greatly appreciated.

Thanks

 

[RiverGuy]

Well, first thing, I wouldn't use Float for Sales Amount. I would rather use Decimal or Money.

But here is an sample to get that 952

DECLARE @F FLOAT
SELECT @F = 5565295.24

SELECT RIGHT(CONVERT(VARCHAR(20), CONVERT(INT, (@F * 10))), 3)

 

[ousoonerjoe]

Try CASTing the value as a DECIMAL(16,2) like shown in this example:

DECLARE @x	FLOAT
SET @x = 5565295.24

SELECT CHARINDEX('.', CAST(@x AS DECIMAL(16,2))) AS Val, @x

SET @x = 548942.5
SELECT RIGHT(CAST(@x AS DECIMAL(16,2)),5)

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[Zurich98]

Thank you all for your help. I'll try out and post the resut.

River Guy,

I definitely agree with you on the data type. I'm not the architecture of this db so not sure why whoever deciced the data type as float to store the sales amount.

 

[PWise]

what about

Select ((Amount_*.01)-convert(int,(Amount_*.01)))*100