how to get 2 digits after decimal point in tsql?
Sql ServerTsqlFormattingSql Server Problem Overview
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?
Sql Server Solutions
Solution 1 - Sql Server
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 - Sql Server
You could cast it to DECIMAL and specify the scale to be 2 digits
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 - Sql Server
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 - Sql Server
Your format syntax is wrong actual syntax is
FORMAT ( value, format [, culture ] )
Please follow this link it helps you
Solution 5 - Sql Server
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.
Solution 6 - Sql Server
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.
Solution 7 - Sql Server
SELECT CAST(12.0910239123 AS DECIMAL(15, 2))
Solution 8 - Sql Server
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')