Retuning Fomated number from a query

Question

Post reply

Retuning Fomated number from a query

Chaz-265757

Old Hand

Points: 333
More actions
December 29, 2005 at 10:27 am

#60720

Hi to all,

I am not sure this is where I post this question.

I trying to find a way to return the results from this query.

I have column A value = ‘1.6025’

How can I return it from the table as “1.60”

Select A from TableName

Results
--------
1.6025

this is what I need
Results
--------
1.60

Thanks
Chaz
Kindest Regards,
Chaz

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

round()?

declare @i numeric( 6, 4)

select @i = 1.6025

select @i, round(@i, 2)

Chaz-265757 Old Hand Points: 333 More actions · Answer 2

I’ve tried that but it is not working.

Here is how it looks in the table

ColA ColB ColC ColD

-------- -------- ------- ------

432855 18893 3601 19.060000

This is how I need it to look after using a select statement

ColA ColB ColC ColD

--------- --------- ------- ------

432,855 18,893 3,601 19.06

Is there a function in SQL like numberformat(), or anything that will format numbers?

thanks for your respond

Kindest Regards,

Chaz

SQLBill SSC Guru Points: 51440 More actions · Answer 3

I believe the closest you can come in SQL Server (this is something that is normally left to be done on the front-end), is to use MONEY.

SELECT CONVERT(MONEY, ColA, 1) AS ColA,

CONVERT(MONEY, ColB, 1) AS ColB,

CONVERT(MONEY, ColC, 1) AS ColC,

CONVERT(MONEY, ColD, 1) AS ColD

FROM tablename

-SQLBill

Chaz-265757 Old Hand Points: 333 More actions · Answer 4

Thanks SQLBill

This did not give me what I am looking for.

I'm going to dump the results in excel and format it that way for a quick fix.

I was trying to look for a quicker way to get the results to the user.

Thanks

Kindest Regards,

Chaz

ericwallace SSC Enthusiast Points: 152 More actions · Answer 5

Try using the STR function such as this:

declare @i numeric( 6, 4)

select @i = 1.6075

select @i, str(@i, 6, 2)

Chaz-265757 Old Hand Points: 333 More actions · Answer 6

Thanks ericwallace,

that works fine, now how do i add the ',' to it.

i.e.

2794

2,794

Kindest Regards,

Chaz

ericwallace SSC Enthusiast Points: 152 More actions · Answer 7

Hi Chaz,

I finally came across the answer to this question. Don't know if it is too late or not, but here it is:

declare @i numeric( 12, 4)

select @i = 6582321.6075

SELECT CONVERT(varchar,CAST(@i AS money),1)

This returns result of 6,582,321.61

I did not realize that money is actually similar to format of decimal(19,4) and to get the style you want you have to CONVERT the money to character format with style 1 after you have CAST the numeric as money. It makes perfect sense after you understand all the details.

Eric

David Burrows SSC Guru Points: 65142 More actions · Answer 8

SELECT

REPLACE(CONVERT(varchar,CAST(ColA as money),1),'.00',''),

REPLACE(CONVERT(varchar,CAST(ColB as money),1),'.00',''),

REPLACE(CONVERT(varchar,CAST(ColC as money),1),'.00',''),

CONVERT(varchar,CAST(ROUND(ColD,2,1) as money),1)

FROM [TableName]

Beware of rounding

SELECT CONVERT(varchar,CAST(1.6075 as money),1)

Result: 1.61

SELECT CONVERT(varchar,CAST(ROUND(1.6075,2,1) as money),1)

Result: 1.60

Far away is close at hand in the images of elsewhere.
Anon.

ericwallace SSC Enthusiast Points: 152 More actions · Answer 9

David,

Good suggestion on eliminating the .00 on items with no pennies, but I believe he wants it to round as opposed to truncating, which the convert will do without using round.

Eric