How to format number with dot

How can I format numer as

40946.591532

I want it format us 40946,59 or 40946

Is it possible in t-sql ?

CodePudding user response：

SELECT CAST(40946.591532 as numeric(18,2))

CodePudding user response：

You can FORMAT your value, changing the decimal to a comma, by using the General Format Specifier (G) and the Polish Culture Code (pl) after CASTING the value to NUMERIC(18,2).

Culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL Server. If the culture argument is not valid, FORMAT raises an error.

See FORMAT with numeric types for more details.

SELECT FORMAT(CAST(40946.591532 AS NUMERIC(18,2)), 'G', 'pl') AS Number

Result:

| Number   |
|----------|
| 40946,59 |

You can also use FLOOR() to return an integer value:

The FLOOR() function returns the nearest integer that is less than or equal to the specified number or numeric expression.

SELECT FLOOR(40946.591532) AS Number

Result:

| Number  |
|---------|
| 40946   |

Demo here.