Using SQL to preserve two decimal places

SQL preserves two-digit decimal places implementation

There are three methods to retain two decimal places in SQL (the second and third methods are similar):

1. Use the ROUND() function

ROUND returns a value, rounded to the specified length or precision, use example:

SELECT ROUND(123.9994, 3)   --123.9990
SELECT ROUND(123.9995, 3)   --124.0000
SELECT ROUND(748.584, -1)   --750.000
SELECT ROUND(748.586, -2)   --700.000
SELECT ROUND(748.586, -3)   --Error WillexpressionConvert to data type numeric Arithmetic overflow error occurred while
SELECT ROUND(748.586, -4)   --0.000,iflengthIt is a negative number,And the number of numbers before the decimal point，butROUNDWill返回 0
SELECT ROUND(151.75, 0,0)   --152.00 Rounding
SELECT ROUND(151.75, 0,1)   --151.00 Cutoff

2. Use the CONVERT() function

Example of usage:

SELECT CONVERT(DECIMAL(13,2),13.123) --13.12 

3. Use the CAST() function

Example of usage:

SELECT CAST(13.123 as DECIMAL(13,2)) --13.12 

There are still differences between these three methods (actually two).

The difference is that ROUND() just rounds, and it will still retain the following digits as 0, but convert() and cast() will truncate the following digits

Example:

SELECT CONVERT(DECIMAL(13,2),13.123)    --13.12
SELECT CAST(13.123 as DECIMAL(13,2))    --13.12
SELECT ROUND(13.123,2)                  --13.120

According to the requirements, the second solution is adopted, but it is not enough to process it like this. The returned float type will still discard the last "0" bit after the code is processed.

The final solution is:

During database query, the float type data is converted into character type, and then handed over to the code to process and return it to the client.

The specific implementation is:

SELECT RTRIM(CONVERT(DECIMAL(13,2),13.123)) 

Attached with the usage of RTRIM(), it returns a character expression with trailing spaces removed.

RTRIM(character expression)

Example:

SELECT RTRIM('Hello   ')+'%'                    --Hello%
SELECT RTRIM('Hello')+'%'                       --Hello%
SELECT RTRIM('hello ' + '' + 'world ')+'%'      --helloworld%
SELECT RTRIM('hello ' + '' + 'world ')+'%'      --hello world%
SELECT RTRIM('hello' + ' ' + 'world ')+'%'      --hello world%

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.