how to get 2 digits after decimal point in tsql?

Solution 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

Solution 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.

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

Solution 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

Output

1.00
2.00
3.00

Solution 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

Solution 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.