CODEWITHSUNDEEP
sqlsql-serverdecimal
Nishant Rana
Nishant Rana

Reputation: 13

How to format decimal number to custom format in SQL Server?

I want to convert decimal number to specific format.

FROM 123456789.1234 to 12,34,56,789.00.

I tried it using 

SELECT FORMAT(123456789.1234, 'N', 'en-us') as 'en-us'

but this returns:

123,456,789.12

I also tried 

SELECT FORMAT(123456789.1234, '##,###.00') as 'en-us'

and got the same result.

Any suggestions on this?

Upvotes: 1

Views: 384

Answers (5)

shawnt00
shawnt00

Reputation: 17915

replace(ltrim(format(d, '## ## ## ## ###\.\0\0')), ' ', ',')

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If, you don't want to use decimal values you could simply cast them as INT/BIGINT

SELECT FORMAT(cast(123456789.1234 as bigint), 'N', 'en-In')

Use culture parameter en-In for Indian Numbering system instead of Western Numbering system en-us look for Numbering system

Upvotes: 0

DatabaseCoder
DatabaseCoder

Reputation: 2032

Use ROUND OR FLOOR function with FORMAT function -

SELECT FORMAT(ROUND(123456789.1234,0), 'N', 'en-IN') as 'Round',
       FORMAT(FLOOR(123456789.1234), 'N', 'en-IN') as 'Floor'

Please check difference between Round and Floor function.

Upvotes: 1

mrs23
mrs23

Reputation: 1

Just try round() in inner side. you also want "12,34,56,789.00" format. So, for that you can change regional setting of your machine.

Upvotes: 0

Chris Meurer
Chris Meurer

Reputation: 468

The proper way to do this would probably be Round()

SELECT FORMAT(ROUND(123456789.1234,0), 'N', 'en-us') as 'en-us'

Upvotes: 0

Related Questions