Formatting Numbers

[pcdaveh]

I'm constructing a query with a float data type. I need to display the number as ###,###.## How do I get the number to display in this format with out changing the data type in the table?

 

[fluteplr]

You do not say what version of sql server you are using.
This function will work in 2000. That being said, SQL server is not really designed for formatted output that is what report writers are for.

CREATE FUNCTION dbo.fnformatnumber  (@number float, @decimal  int =0)  
RETURNS varchar(30) AS  
BEGIN 
declare @retval varchar(30),
	@pos int

if @number is null
begin
	set @number = 0 
end

set @retval = rtrim(ltrim(str(round(abs(@number),@decimal),20,@decimal)))

set @pos=charindex('.',@retval)
If @pos=0
 	Set @pos=len(@retval)-2
Else
	 Set @pos=@pos-3

While @pos>1
 Begin
	  Set @retval=stuff(@retval,@pos,0,',')
	  Set @pos=@pos-3
 End


if @number < 0 
begin
	set @retval = '('+@retval+')'
end

return @retval 
END

 

[pcdaveh]

Thanks for your post. I'm using SQL 2000. I'm actually using Access ADP project. I need to show on the screen in a list box the number with decimal places. The list box doesn't have a property to set comma or decimal. That's why I'm approaching it from this angle.

Thanks

 

[MaxZak]

If you're using Access you can format numbers in the query you use to populate the listbox, the Format() function in Access works also in query statements

 

[pcdaveh]

I'm not sure if any of you have used an Access Data Project (*.adp) before but you end up using Access on top of MSDE. Your SQL designer is no longer Access but SQL Server. The syntax that must be used is T-SQL. When I created my first *.adp I didn't realize that I was "on the server" I thought it would be more like being in access using an odbc link to a sql table. Becareful if your new to using these projects an your used to using access as I was using ODBC. The tables in your project are not just links like they are using odbc. They are the live tables in you sql server database. I unknowingly deleted tables that I didn't need thinking that they were links. I killed the production database by accident. So you can't as far as I know use the Format command while using an adp in the SQL Server Designer.

Thanks Though!