CODEWITHSUNDEEP
sql
nairware
nairware

Reputation: 3190

SQL - Set Decimal Value Using Money

I am trying to take an existing column with money values in it, and use those to set a value in another column which is a decimal. As an example, if I have "8.65" as my money value, I am trying to set a decimal value/column associated with that same record as "8.6500000000". In my case, I need 10 decimal points of precision.

This is the code I tried which failed to do anything (it did not give an error, it just did not update any records):

SET abc_PaidDecimal = CAST(abc_PaidAmnt AS DECIMAL(16,10))
WHERE abc_PaidDecimal <> CAST(abc_PaidAmnt AS DECIMAL(16,10))

Upvotes: 0

Views: 1018

Answers (3)

ClearLogic
ClearLogic

Reputation: 3682

You don't need to explicitly Cast money to decimal ,it will be converted implicitly and stored as decimal(16,10)

however to do comparison between 8.65 and 8.650000 cast both sides to varchar(26) when you don't want them to be treated equal.

WHERE   CAST(abc_PaidDecimal AS VARCHAR(26)) <> CAST(abc_PaidAmnt AS VARCHAR(26))

an example 

DECLARE @v1 MONEY = 23.25
        ,@v2 DECIMAL(18,6)= 23.250000

SELECT @v1 , @v2
IF  @v1 = @v2
SELECT 'same value before cast'
ELSE 
SELECT 'different value before cast'

SELECT CAST(@v1 AS VARCHAR(26)) , CAST(@v2 AS VARCHAR(26))
IF  CAST(@v1 AS VARCHAR(26)) = CAST(@v2 AS VARCHAR(26))
SELECT 'same value after cast'
ELSE 
SELECT 'different value after cast'

Upvotes: 0

Michael Domashchenko
Michael Domashchenko

Reputation: 1480

You can get this behavior if your abc_PaidDecimal contains NULL. NULLs cannot be compared to any other value, the only valid comparison for them is to use IS NULL or IS NOT NULL.

So, try adding or abc_PaidDecimal IS NULL to your WHERE clause

Upvotes: 1

Gonzalo.-
Gonzalo.-

Reputation: 12672

8.65 and 8.6500000000000 are the same number.

Also 1 and 00001 are the same numbers. So, in your where clause, <> will always give false. That's why you're not updating anything

If you want to update all your fields, with this transformation, remove the Where clause

Upvotes: 0

Related Questions