CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Akhil Rajan
Akhil Rajan

Reputation: 43

spliting a decimal values to lower decimal in sql server

How can i split a decimal value into two decimal values. if the decimal number which has fractional part that is less than .50 or greater than .50 .it should split in such a way that first no should end with only .00 or .50. second value should contain the remaining factorial value.

ex.  19.97 should return 19.50 & 0.47
     19.47               19.00 & 0.47

Upvotes: 1

Views: 1381

Answers (4)

D Stanley
D Stanley

Reputation: 152576

You can "floor" to the highest multiple of 0.5 by multiplying by 2, calling FLOOR, then dividing by 2. From there just subtract that from the original value to get the remainder.

DECLARE @test decimal(10,7)
SELECT @test =19.97

SELECT 
    FLOOR(@test * 2) / 2 AS base, 
    @test - FLOOR(@test * 2) / 2 AS fraction

or to reduce duplication

SELECT 
    base, 
    @test - base AS fraction
FROM ( SELECT FLOOR(@test * 2) / 2 AS base )

Upvotes: 3

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726779

Observe that doubling 0.5 gives 1, which is a whole number. This leads to a simple algorithm:

  • Double the number
  • Split it into a whole and a fractional parts by using floor(x) and x-floor(x)
  • Divide each part separately by 2 to give you the results that you need.

Let's take your numbers as an example:

19.97 * 2 = 39.94
Whole part = 39, fractional part = 0.94
Dividing each part by 2 individually, we get
39/2 = 19.50
0.94/2 = 0.47

19.47 * 2 = 38.94
Whole part = 38, fractional part = 0.94
Dividing each part by 2 individually, we get
38/2 = 19.00
0.94/2 = 0.47

Upvotes: 1

user1509107
user1509107

Reputation:

You would need to look into the MODULO operator in SQL Server.

SELECT 
19.97 as myDecVal , 
19.97 % 0.5 AS decGreaterThan50,/*should return 0.47*/ 
19.97- (19.97 % 0.5) as roundedToNearestZeroPoint5 /*should return 19.50*/;

Upvotes: 0

LHH
LHH

Reputation: 3323

Declare @money money

Set @money = 19.97

Select  convert(int,@money - (@money % 1))  as 'LeftPortion'
       ,convert(int, (@money % 1) * 100)    as 'RightPortion'

Upvotes: 1

Related Questions