[sql-server] how to get 2 digits after decimal point in tsql?

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

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.

How can I format this calculation?

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.

Your format syntax is wrong actual syntax is

 FORMAT ( value, format [, culture ] )

Please follow this link it helps you

Click here for more details

SELECT CAST(12.0910239123 AS DECIMAL(15, 2))

Assume that you have dynamic currency precision

• value => 1.002431

• currency precision => 3

• `result => 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')

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

Output

1.00
2.00
3.00

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.

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

DECLARE @i AS 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.