Formatting Numeric Output for Decimal Places

Question

Post reply

Formatting Numeric Output for Decimal Places

Anthony Lukindo

SSC-Addicted

Points: 407
More actions
July 28, 2004 at 4:46 pm

#161289

Hi
Can somehow shine some light on how to format the output of a numeric column to conform to a desired number of decimal places?
I have used the STR() function but ran into problems when sorting the resulting column. When using STR() function the the numeric idata is turned into a String and thus '100' apears less than '20' in the sort order!!!
I want to format the output and still retain the numeric data type.
Anthony

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Hi Anthony

Try using the decimal function:

Eg

select cast(14.1 as decimal(19,7)) as dp7, cast(14.1 as decimal(19,2)) as dp2

etc.

Check BOL for a full description of the function arguments.

Regards

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Several ideas I have here http://www.insidesql.de/content/view/252/

Although it's in German, I'm sure you'll get the meaning. And may I add that I think that this is a job for your front-end, not the server?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Tony Hinkley SSC-Addicted Points: 435 More actions · Answer 3

Surely you could return the field formatted as a string but use the original numeric in the ORDER BY? Or is the sorting being performed client side? In which case I agree with Frank, why not perform the formatting there?

Tony

Anthony Lukindo SSC-Addicted Points: 407 More actions · Answer 4

Thanks for all who contributed a solution to this. A few things are noteworthy:

The formatting method using:

CAST(@MyNumeric AS DECIMAL(8,2)) works OK and data is returned correctly under SQL Query Analyzer.

However,when executing the same Sp in VB 6. Any trailing '0' decimal numerics are truncated. I am using an ODBC SQL Server driver for this. . . . . . Does anyone know why?

So rather than:

2.00 I get 2

2.10 I get 2.1

3.00 I get 3.00

NEXT: Yes, sorting by the original column name works great when using the STR function to format the output

A gotcha was that I aliased the formatted result using the same name as the original column name. For some reason it honored the character data type when this was done.

The same happened when I modified the order by clause to read 'ORDER BY TableName.ColumnName'

Anyway the problem has been fixed after giving the formattted column an unique aliased name.

ButchH Ten Centuries Points: 1073 More actions · Answer 5

If you're presenting the results of this query on the web, you might need to use a align="right" tag and this will format the number to TWO decimal places:

<%=FormatNumber(rs("BD_TR_AMT"),2,-1)%>

Hope this helps.

Butch

Tony Hinkley SSC-Addicted Points: 435 More actions · Answer 6

The trailing zero's get truncated (or rather not displayed) by VB as it does not see them as significant figures. When you look at the definition for the primitive data types it tells you how much data each can store, but doesn't mention that VB only shows significant figures when it outputs. Any calculations you do with the numbers however should still be at the right degree of accuracy.

Like Butch said, the FormatNumber command is really useful, and there are more parameters that Butch hasn't included. Should show up in the intellisense in VB6.

Tony