December 29, 2005 at 10:27 am
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
Chaz
December 29, 2005 at 10:37 am
round()?
declare @i numeric( 6, 4)
select @i = 1.6025
select @i, round(@i, 2)
December 29, 2005 at 10:53 am
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
Chaz
December 29, 2005 at 10:57 am
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
December 29, 2005 at 11:16 am
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
Chaz
December 30, 2005 at 10:02 am
Try using the STR function such as this:
declare @i numeric( 6, 4)
select @i = 1.6075
select @i, str(@i, 6, 2)
December 30, 2005 at 10:16 am
Thanks ericwallace,
that works fine, now how do i add the ',' to it.
i.e.
2794
2,794
Chaz
March 2, 2006 at 8:37 pm
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
March 7, 2006 at 7:21 am
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.
March 7, 2006 at 9:25 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply