TSQL - how to avoid truncating or rounding

Question

For some reason, the data in the ORIGINAL_BOOK column (even though it has 2 decimals (eg. 876.76)), the statement below truncates the decimals. I want the decimals to be visible as well. Can someone suggest how to fix this issue please?

Case 
   When [DN].[SETTLEMENT_DATE] > [DN].[AS_OF_DATE] 
      Then Cast([DN].[ORIGINAL_BOOK] as decimal(28, 2))
      Else Cast([DN].[CURRENT_BOOK] as decimal(28, 2))
End

What is the data type of `CURRENT_BOOK`? How are the values stored there? Look at culture specific differences such as dot or comma to separate the decimals if this is a string value... — Shnugo, Oct 12 '16 at 19:36
In what way are the decimal places _invisible_? As written the `CASE` expression will always return a value with two decimal places (or NULL). What do you do with that value? — HABO, Oct 12 '16 at 20:09

score 1 · Answer 1 · answered Nov 23 '16 at 14:41

I can't be sure because you only say that the type of the fields involved is NUMERIC, without specifying any precision or scale, however if your source fields really are just NUMERIC type, SQL defaults to NUMERIC(18,0) (as per MSDN documentation here) and so you will only be able to store values with a scale of zero (i.e. no value after the decimal place) and any values written to these fields with a greater scale (i.e. data after the decimal place) will be rounded accordingly:

CREATE TABLE dn (
    ORIGINAL_BOOK NUMERIC,
    CURRENT_BOOK NUMERIC
)

INSERT INTO dn
SELECT 876.76, 423.75
UNION
SELECT 0, 0
UNION
SELECT 1.1, 6.5
UNION
SELECT 12, 54
UNION
SELECT 5.789, 6.321

SELECT  CAST(dn.ORIGINAL_BOOK AS DECIMAL(28,2)) AS ORIGINAL_BOOK_CONV,
        CAST(dn.CURRENT_BOOK AS DECIMAL(28,2)) AS CURRENT_BOOK_CONV
FROM dn

DROP TABLE dn

gives results:

/----------------------------------------\
| ORIGINAL_BOOK_CONV | CURRENT_BOOK_CONV |
|--------------------+-------------------|
| 0.00               | 0.00              |
| 1.00               | 7.00              |
| 6.00               | 6.00              |
| 12.00              | 54.00             |
| 877.00             | 424.00            |
\----------------------------------------/

Increasing the scale of the field in the table will allow values with greater numbers of decimal places to be stored and your CAST call will then reduce the number of decimal places if appropriate:

CREATE TABLE dn (
    ORIGINAL_BOOK NUMERIC(28,3),
    CURRENT_BOOK NUMERIC(28,3)
)

INSERT INTO dn
SELECT 876.76, 423.75
UNION
SELECT 0, 0
UNION
SELECT 1.1, 6.5
UNION
SELECT 12, 54
UNION
SELECT 5.789, 6.321

SELECT  CAST(dn.ORIGINAL_BOOK AS DECIMAL(28,2)) AS ORIGINAL_BOOK_CONV,
        CAST(dn.CURRENT_BOOK AS DECIMAL(28,2)) AS CURRENT_BOOK_CONV
FROM dn

DROP TABLE dn

gives results:

/----------------------------------------\
| ORIGINAL_BOOK_CONV | CURRENT_BOOK_CONV |
|--------------------+-------------------|
| 0.00               | 0.00              |
| 1.10               | 6.50              |
| 5.79               | 6.32              |
| 12.00              | 54.00             |
| 876.76             | 423.75            |
\----------------------------------------/

If you are sure that your table fields are capable of containing numeric values to more than zero decimal places (i.e. scale > 0), please post the CREATE TABLE script for the table (you can get this from SSMS) or a screenshot of the Column listing so we can see the true type of the underlying fields. It would also be useful to see values SELECTed from the fields without any CASTing so we can see how the data is presented without any conversion.

TSQL - how to avoid truncating or rounding

1 Answers1