How to cut off or add decimals based on values in a filed - SQL server

Question

Here is a sample code

declare @Currency table (ccy char(3),amount money)
insert into @Currency
values ('USD',12.34), ('AUD',12.34), ('INR',12.34)

I need to show the output as below,

when ccy = 'USD' then 12 (No decimals- cut off after decimal values) 

when ccy = 'AUD' then 12.34 (Two decimals)

when ccy = 'INR' then 12.3400 (Four decimals)

Expected Output:

 ccy    amount

 USD    12
 AUD    12.34
 INR    12.3400

Please advice how do I achieve this in Tsql.

Reference: https://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server Consider using Decimal datatype instead of money. additionally I generally consider formatting as a presentation element; not something the database does: but if you must cast to varchar with desired format. — xQbert, May 11 '18 at 12:27
@Jaberwocky Op specified he needs this format only for output reasons — B3S, May 11 '18 at 12:30
Either way, this is beyond the scope of the money data type. — addohm, May 11 '18 at 12:31
Why do this in *SQL* instead of the *client*? It's far easier to specify the decimals when formatting the value for display, eg by writing `total.ToString("N4")` where `digits is the number of digits per currency. The equivalent `FORMAT` function was added in SQL Server 2012 — Panagiotis Kanavos, May 11 '18 at 12:36

score 4 · Answer 1 · answered May 11 '18 at 12:29

4

Try this:

declare @Currency table (ccy char(3),amount money)
insert into @Currency
values ('USD',1234), ('AUD',1234), ('INR',1234)

SELECT ccy,
    CASE WHEN ccy = 'USD' THEN CAST(CAST(amount AS NUMERIC(16,0)) AS VARCHAR)
         WHEN ccy = 'AUD' THEN CAST(CAST(amount AS NUMERIC(16,2)) AS VARCHAR)
         WHEN ccy = 'INR' THEN CAST(CAST(amount AS NUMERIC(16,4)) AS VARCHAR)
    END amount
FROM @Currency

Output:

ccy amount
USD 1234
AUD 1234.00
INR 1234.0000

Check the answer in the # SQL Fiddle

answered May 11 '18 at 12:29

DineshDB

5,998
7
33
49

This is actually a good generic solution that works in almost any databases. However, I strongly, strongly advise you to never use `varchar()` or related types without a length parameter. The default length varies by context, and depending on it can introduce very hard to debug problems. – Gordon Linoff May 11 '18 at 12:39
@GordonLinoff, Thank you..! I'll follow it in the future. Thanks a lot for the advice. – DineshDB May 11 '18 at 12:40

score 3 · Accepted Answer · answered May 11 '18 at 12:36

You need to convert the value to a string. SQL Server has a convenient function for this, str():

select ccy,
       (case when ccy = 'USD' then str(amount, 10, 0)
             when ccy = 'AUD' then str(amount, 10, 2)
             when ccy = 'INR' then str(amount, 10, 4)
        end)
from @currency;

You may also want to investigate the format() function. This allows you to include currency symbols as well.

score 0 · Answer 3 · answered May 11 '18 at 12:29

0

SELECT CCY,
       CASE WHEN CCY=USD THEN ROUND(AMOUNT, 0)
            WHEN CCY=AUD THEN ROUND(AMOUNT, 2)
            WHEN CCY=INR THEN ROUND(AMOUNT, 4)
       END
FROM @CURRENCY

answered May 11 '18 at 12:29

B3S

1,021
7
18

How to cut off or add decimals based on values in a filed - SQL server

3 Answers3