Custom number format

Hello everybody,
I have a float type field in my sql server table and I want to select the
data in the format $XX,XXX.XX.
Any ideas '
Thanks.usamaalam wrote:

> Hello everybody,
> I have a float type field in my sql server table and I want to select the
> data in the format $XX,XXX.XX.
> Any ideas '
> Thanks.
Your client application controls how data is formatted for display, not
SQL Server. If you want to do that in SQL you'll have to return a
string rather than a number. I don't recommend it, but you can take a
look at the CONVERT function in Books Online.
Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
so is an unusual and probably unwise choice for accounting. More
typically, I would suggest you should use NUMERIC/DECIMAL for this.
Hope this helps.
David Portas
SQL Server MVP
--|||I need select float in the specified format and return the formatted string.
Thanks.
"David Portas" wrote:

> usamaalam wrote:
>
> Your client application controls how data is formatted for display, not
> SQL Server. If you want to do that in SQL you'll have to return a
> string rather than a number. I don't recommend it, but you can take a
> look at the CONVERT function in Books Online.
> Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
> so is an unusual and probably unwise choice for accounting. More
> typically, I would suggest you should use NUMERIC/DECIMAL for this.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||As David says, you should do it in the application tier and not in the
database. If you are using .NET in your application tier, there are powerful
formatting capabilities.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
news:8207FDF2-C83E-491A-AE06-FF91FD965E14@.microsoft.com...
>I need select float in the specified format and return the formatted
>string.
> Thanks.
> "David Portas" wrote:
>|||Basically the application mechanism has been developed by someone else. I
need to export data from some tables in CSV format. The mechanism executes
an stored procedure, get data in a data table and a method gets the data
table as a parameter and generates CSV whatever present in the data table.
This will be more helpful for me to do it in sql, if possible.
Thanks a lot.
"SriSamp" wrote:

> As David says, you should do it in the application tier and not in the
> database. If you are using .NET in your application tier, there are powerf
ul
> formatting capabilities.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
> news:8207FDF2-C83E-491A-AE06-FF91FD965E14@.microsoft.com...
>
>|||One method:
SELECT
'$' + CONVERT(varchar(20), CAST(MyFloatColumn AS money), 1)
FROM MyTable
I agree with the others that this is a kludge. This is a deficiency in the
application that ought to be addressed in the app code rather than
Transact-SQL.
Happy Holidays
Dan Guzman
SQL Server MVP
"usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
news:874B2D06-CA54-4852-AB97-4EB07762DA84@.microsoft.com...
> Basically the application mechanism has been developed by someone else. I
> need to export data from some tables in CSV format. The mechanism
> executes
> an stored procedure, get data in a data table and a method gets the data
> table as a parameter and generates CSV whatever present in the data table.
> This will be more helpful for me to do it in sql, if possible.
> Thanks a lot.
> "SriSamp" wrote:
>|||i've needed to do this myself a number of times, format the output on the
SELECT because i'm using BCP to plonk the data into an output file ready for
FTP'ing off to another server somewhere for another department/company to
use.
I'm not going to go to the effort of building a .NET application just to do
this; far simpler and quicker to format on the SELECT and output using BCP -
less components, less complexity.
There are times in reporting too where we need to do this because it would
be more difficult to do it in the reporting tool, for instance reporting
services; consider a trading report where trade amounts might be in
different currency, two ways of doing it - either concatenate in the front
end which might be more difficult or just simply do it easily on the SELECT.
You need to consider what you are doing before definitively saying do it in
the front end or in the db.
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1135337208.989446.250540@.g43g2000cwa.googlegroups.com...
> usamaalam wrote:
>
> Your client application controls how data is formatted for display, not
> SQL Server. If you want to do that in SQL you'll have to return a
> string rather than a number. I don't recommend it, but you can take a
> look at the CONVERT function in Books Online.
> Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
> so is an unusual and probably unwise choice for accounting. More
> typically, I would suggest you should use NUMERIC/DECIMAL for this.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Maybe you should consider using XML as a transport medium, instead of CSV.
After all, this is the 21st century. :)
If the data is being used in an application at the other end then I really
see no point in formatting it, unless the actual data type is ignored at the
other end, which I doubt to be true.
ML
http://milambda.blogspot.com/|||i just want to reiterate what David said: if at all possible, change the
float to another type - you *will* get bad data at some point, and this
being money, that will be a problem.
if not possible for you to do it, hound the person who can do it :)
usamaalam wrote:
> Hello everybody,
> I have a float type field in my sql server table and I want to select the
> data in the format $XX,XXX.XX.
> Any ideas '
> Thanks.|||On Fri, 23 Dec 2005 08:17:02 -0800, "ML" <ML@.discussions.microsoft.com> wrot
e:
in <61588BC5-7F5E-4BD4-A95E-7B6B24984F3A@.microsoft.com>

>Maybe you should consider using XML as a transport medium, instead of CSV.
>After all, this is the 21st century. :)
I don't understand that sort of reasoning. What makes a proprietary schema
and
bloated data better than the CSV format?
Stefan Berglund