Geek asks and answers

How can I get the decimal fraction in sql

I have a decimal value of 34.3287332, how can I get a fraction of the value, for example .3287332, please help me (I can convert this to a string and get a fraction, but I don't need to)

+1
source share
3 answers

I would just get the whole value and then get the remainder:

decimal total = /* get value from database */;
decimal fraction = decimal.Remainder(total, 1m);

(Perhaps a more efficient way to get the rest, but it's pretty simple.)

While you can do this in SQL, it might work if you want to get values ​​from LINQ to SQL or something like that - I prefer to manipulate the value in .NET code rather than in SQL if it does not affect results or performance.

+2
source

FLOOR():

SELECT Value-FLOOR(Value)

:

34.3287332 - 34 = 0.3287332
+5

Note that the above FLOOR clause will not work correctly for negative numbers; you need to do ABS(my_float_value) - FLOOR(ABS(my_float_value))to get the right answer.

If you find that you are using the SQLite backend, you will find that it does not have the FLOOR function, and the trick "my_float_value% 1" is also invalid. Then you can apply CAST to get equivalent functionality: ABS(my_float_value) - CAST(ABS(my_float_value) AS INTEGER)

+2
source

More articles:


All Articles