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

Question

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?

do you really mean this format 12,34,56,789.00? maybe you mean 123,456,789.00. — John Woo
– John Woo, Commented Feb 28, 2018 at 5:55
OK, I'll say it ... Formatting really belongs in the presentation layer. — John Cappelletti
– John Cappelletti, Commented Feb 28, 2018 at 6:14
@shawnt, if number is 123 only, above format will show somthing ,,123.00. — Nishant Rana
– Nishant Rana, Commented Feb 28, 2018 at 9:52

DatabaseCoder · Accepted Answer · 2018-02-28 06:20:37Z

1

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.

edited Feb 28, 2018 at 6:20

answered Feb 28, 2018 at 6:12

DatabaseCoder

2,0422 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

John Cappelletti Over a year ago

Was waiting for someone to offer something other than round()

Nishant Rana Over a year ago

@BhatiaAshish - round or floor is not my primary goal. My goal is to achieve number separators. above solution gives me - Round 1234,56,789.00,Floor 1234,56,789.00 , which did not help me.

DatabaseCoder Over a year ago

@NishantRana, Above query gives me 12,34,56,789.00 which is as per requirement. Not sure why this not working on your machine.

Chris Meurer · Accepted Answer · 2018-02-28 05:54:15Z

0

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

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

answered Feb 28, 2018 at 5:54

Chris Meurer

4703 silver badges13 bronze badges

Comments

mrs23 · Accepted Answer · 2018-02-28 06:03:29Z

0

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.

answered Feb 28, 2018 at 6:03

mrs23

11 bronze badge

Comments

Yogesh Sharma · Accepted Answer · 2018-02-28 06:40:24Z

0

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

edited Feb 28, 2018 at 6:40

answered Feb 28, 2018 at 6:30

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

4 Comments

Nishant Rana Over a year ago

This doen't help me exactly with my requirement. I need to show 12,56,789. Above is showing me 1234,56,789

Yogesh Sharma Over a year ago

@NishantRana.. what SQL Version u r using ?

Nishant Rana Over a year ago

SQL Server 2012

Yogesh Sharma Over a year ago

@NishantRana... make sure your culture parameter has value of en-In instead of en-us.

shawnt00 · Accepted Answer · 2018-02-28 13:26:11Z

0

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

answered Feb 28, 2018 at 13:26

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

3 Comments

Comments

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related