如何在tsql中获得小数点后的2位数字?

I am having problem to format digits in my select column.I used FORMAT but it doesn't work. Here is my column:

在我的select列中，我有格式化数字的问题。我用了格式，但不行。这是我的专栏:

sum(cast(datediff(second, IEC.CREATE_DATE, IEC.STATUS_DATE) as float) / 60) TotalSentMinutes    

I used this:

我用这个:

FORMAT(sum(cast(datediff(second, IEC.CREATE_DATE, IEC.STATUS_DATE) as float) / 60),2) TotalSentMinutes  

ERROR:

错误:

'format' is not a recognized built-in function name.

“format”不是公认的内置函数名。

How can I format this calculation?

我如何格式化这个计算?

8 个解决方案

#1

Try this one -

试试这个,

DECLARE @i FLOAT = 6.677756

SELECT 
      ROUND(@i, 2)
    , FORMAT(@i, 'N2')
    , CAST(@i AS DECIMAL(18,2))
    , SUBSTRING(PARSENAME(CAST(@i AS VARCHAR(10)), 1), PATINDEX('%.%', CAST(@i AS VARCHAR(10))) - 1, 2)
    , FLOOR((@i - FLOOR(@i)) * 100)

Output:

输出:

----------------------
6,68
6.68
6.68
67
67

#2

You could cast it to DECIMAL and specify the scale to be 2 digits

您可以将它转换为十进制并将比例指定为两位数

decimal and numeric

小数和数字

So, something like

所以,类似

DECLARE @i AS FLOAT = 2
SELECT @i / 3
SELECT CAST(@i / 3 AS DECIMAL(18,2))

SQLFiddle DEMO

I would however recomend that this be done in the UI/Report layer, as this will cuase loss of precision.

但是我要重申，这是在UI/报表层中完成的，因为这会降低精度。

Formatting (in my opinion) should happen on the UI/Report/Display level.

格式化(在我看来)应该发生在UI/报表/显示级别。

#3

Try cast result to numeric

尝试将结果转换为数值

CAST(sum(cast(datediff(second, IEC.CREATE_DATE, IEC.STATUS_DATE) as float) / 60)
    AS numeric(10,2)) TotalSentMinutes

Input

输入

1
2
3

1 2 3

Output

输出

1.00
2.00
3.00

1.00 2.00 3.00

#4

Your format syntax is wrong actual syntax is

你的格式语法是错误的

 FORMAT ( value, format [, culture ] )

Please follow this link it helps you

请按照这个链接来帮助你。

Click here for more details

点击这里了解更多细节

#5

So, something like

所以,类似

DECLARE @i AS FLOAT = 2
SELECT @i / 3
SELECT CAST(@i / 3 AS DECIMAL(18,2))

I would however recomend that this be done in the UI/Report layer, as this will cuase loss of precision.

但是我要重申，这是在UI/报表层中完成的，因为这会降低精度。

#6

DECLARE @i AS FLOAT = 2 SELECT @i / 3 SELECT cast(@i / cast(3 AS DECIMAL(18,2))as decimal (18,2))

声明@i为FLOAT = 2 SELECT @i / 3 SELECT cast(@i / cast) (3 AS DECIMAL(18,2)) AS DECIMAL(18,2)

Both factor and result requires casting to be considered as decimals.

因素和结果都要求铸造被认为是小数。

#7

Sample: select CAST(12.0910239123 as decimal(15,2))

样本:选择CAST(12.0910239123为decimal(15,2))

#8

Assume that you have dynamic currency precision

假设您具有动态货币精度

• value => 1.002431

  值= > 1.002431

• currency precision => 3

  货币精度= > 3

• `result => 1.002

  '结果= > 1.002

CAST(Floor(your_float_value) AS VARCHAR) + '.' + REPLACE(STR(FLOOR((your_float_value FLOOR(your_float_value)) * power(10,cu_precision)), cu_precision), SPACE(1), '0')

将(Floor(your_float_value)转换为VARCHAR) + '。' + REPLACE(STR(FLOOR(your_float_value FLOOR(your_float_value))) * power(10,cu_precision))， cu_precision)，空格(1)，'0')