Formatting or fixing Decimal places (Pic Credit: Google)

Formatting or fixing Decimal places

Priya Yogendra Rana
4 min readJul 16, 2023

Since the beginning of coding, developer tools have all been about simplifying complex coding while cutting down development time. And the tried tested method has been creating product specific additions to standard code syntax.

While this industry practice does ease a developer’s job, it can get confusing if one is coding across multiple tools or platforms.

Yesterday, I was trying out ways to format decimal places in both Alteryx and SQL. I knew a lot of functions in SQL, but when I delved deeper, I found more functions like TO_CHAR() and TRUNC(). Since the best way to remember is to maintain a diary or notes — so here I am again with a new post.

In Alteryx, by default, all measures were Fixed Decimal and size 24.7. So, I used the Select tool. For maintaining 2 decimal places, set the size of the numeric column as 19.2. Similarly, for 3 decimal places set it as 19.3 and for 4 decimal places, 19.4. Then click the point which connects this select tool to any other tool. It has the look of a green triangle pointing to your right. In the result pane below, please ensure that you select 000 which appears on the extreme right next to Action ˅. It worked for me but I’m not sure if this will work for all scenarios.

In SQL, we can use ROUND(), CAST(), FORMAT(), CONVERT() and STR() functions to set the decimal points. Example: ROUND(342.5738, 2) //output will be 342.57

ROUND(342.5738, 2,0) //output will be 342.57

ROUND(342.5738, 2, 1) //output will be 342.57

ROUND(342.53738, 2, 2) //output will be 342.53

ROUND(342.53738, 2, 0) //output will be 342.54

ROUND(342.5378, 3, 1) //output will be 342.537

ROUND(342.5378, 3, 0) //output will be 342.538

The third part of the function is optional. It can either have 0 or 1. If other than 0 then it truncates the result to the number of decimal.

CAST(342.3738 as DECIMAL(10,2)) //output will be 342.374

CAST(235.415 as decimal(12,2)) //output will be 235.42

CAST(235.4655 as decimal(12,3)) //output will be 235.466

CAST(235.4653 as decimal(12,3)) //output will be 235.465

FORMAT(389.5678, ‘###.##’) //output will be 389.57

FORMAT(185, ‘000.00’) //output will be 185.00

FORMAT(185, ‘###.##’) //output will be 185

CONVERT(DECIMAL(5, 2), 275.846) //output will be 275.85

CONVERT(DECIMAL(5, 1), 275.846) //output will be 275.8

STR(275.849, 6, 2) //output will be 275.85 but in string

STR(275.849, 6, 1) //output will be 275.8 and will be string

In MySQL, we can use FORMAT() function

FORMAT(17654.2574, 3) //output will be 17654.257

FORMAT(200, 4) //output will be 200.0000

TRUNCATE(0.179, 2) //output will be 0.17. The number will be truncated and not rounded

ROUND(0.179, 2) //output will be 0.18 as the number will be rounded depending on the number in the third decimal place.

CAST(543.879 as DECIMAL(5, 2)) //output will be 543.88

CONVERT(77.4567, DECIMAL(5, 2)) //output will be 77.46

In Oracle, we can use TRUNC(), ROUND() and TO_CHAR() functions.

TRUNC(2365.9182, 2) //output will be 2365.91. Note TRUNC() doesn’t support rounding off so the output will not be 2365.92

ROUND() works in the same manner as that in SQL

TO_CHAR(84.7261, ‘fm99D00’) //output will be 84.73

TO_CHAR(84.72617567, ‘fm99D0000’) //output will be 84.7262

TO_CHAR(84.7261, ‘99D00’) //output will be 84.73

TO_CHAR(84.7261, ‘fm99D99’) //output will be 84. (85 followed by dot).

TO_CHAR(84.7261, ‘99D99’) //output will be 84.73

Here, the issue with the TO-CHAR function is that the output will always be in string. ‘fm’ stands for format modifier and will remove any padding that may be applied to the result. No ‘fm’ means you may end up with trailing zeros. Also, the number of 0s you add after D that is the decimal place answer you will get. I would suggest clicking this if you want to know more on this function.

In Snowflake, TRUNC(), ROUND(), CEIL(), FLOOR() functions can be used

TRUNC(123.1232, 3) //output will be 123.123

TRUNC(978.978, 2) //output will be 978.97

ROUND() works in the same manner as that in SQL. However, the difference is in data type.

ROUND(1.008, 2) //output is 1 if 1.008 is float and output will be 1.01 if 1.008 is decimal. In Float the number stored will be larger 1.00786521333 where else in decimal, it is stored as an exact number.

ROUND(7.7, 0) //output will be 8

ROUND(7.7,0,’HALF_TO_EVEN) //output will be 7

FLOOR(167.327, 1) //output will be 167.3

CIEL(167.327, 1) //output will be 167.4

CIEL(167.167) //output will be 168.

Do note, CIEL rounds off to the nearest equal or larger integer with the specified number of decimal places.

However, here I have not covered SQL, Snowflake functions like Round, Ciel and Floor that allow a negative scale i.e. negative decimal place. That’s for another post. But if you want to know then read about it here or here.

I may have missed a few but this seems sufficing. Until next time.

--

--

Priya Yogendra Rana
Priya Yogendra Rana

Written by Priya Yogendra Rana

Ex-Software Developer, MBA, Data Analyst Enthusiast. Bring together business focus and data skills

No responses yet