format number

Question

Post reply

format number

tinafmills

SSC Enthusiast

Points: 197
More actions
January 10, 2017 at 8:59 pm

#401353

How do you display data in thousands with 1 decimal?
10,500 ==> 10.5

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

SELECT

CONVERT(DECIMAL(18,1), 10.500), -- output as a decimal

LTRIM(STR(10.500, 18,1)) -- output as a string

_____________
Code for TallyGenerator

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 2

You could also use

SELECT FORMAT(10.500,'N1')

See documentation for the FORMAT function.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

ThomasRushton (1/11/2017)
You could also use
SELECT FORMAT(10.500,'N1')
See documentation for the FORMAT function.

Although FORMAT is extremely slow compared to CONVERT.

https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 4

GilaMonster (1/11/2017)
ThomasRushton (1/11/2017)
You could also use
SELECT FORMAT(10.500,'N1')
See documentation for the FORMAT function.
Although FORMAT is extremely slow compared to CONVERT.
https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but

Good point.

Mind you, this sort of formatting should be done away from the database layer anyway... 😉

Thomas Rushton
blog: https://thelonedba.wordpress.com

ManicStar SSCoach Points: 17992 More actions · Answer 5

ThomasRushton (1/11/2017)
GilaMonster (1/11/2017)
ThomasRushton (1/11/2017)
You could also use
SELECT FORMAT(10.500,'N1')
See documentation for the FORMAT function.
Although FORMAT is extremely slow compared to CONVERT.
https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but
Good point.
Mind you, this sort of formatting should be done away from the database layer anyway... 😉

What about cast vs format?

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 6

ThomasRushton (1/11/2017)
GilaMonster (1/11/2017)
ThomasRushton (1/11/2017)
You could also use
SELECT FORMAT(10.500,'N1')
See documentation for the FORMAT function.
Although FORMAT is extremely slow compared to CONVERT.
https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but
Good point.
Mind you, this sort of formatting should be done away from the database layer anyway... 😉

Only if there is another layer. I do a lot of formatting (never saved in a permanent table, to be sure) for reports generated by SQL Server that have nothing to do with things like SSRS or other reporting tools because they have to be saved to a file with particular requirements.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 7

Manic Star (1/11/2017)
ThomasRushton (1/11/2017)
GilaMonster (1/11/2017)
ThomasRushton (1/11/2017)
You could also use
SELECT FORMAT(10.500,'N1')
See documentation for the FORMAT function.
Although FORMAT is extremely slow compared to CONVERT.
https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but
Good point.
Mind you, this sort of formatting should be done away from the database layer anyway... 😉
What about cast vs format?

The testing that I've done on format shows that FORMAT is generally 44 times slower than either CAST or CONVERT. Since it's based on .net functionality as a CLR, it makes you wonder how it performs for managed code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)