|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Custom number formatHello 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, Your client application controls how data is formatted for display, not> > 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. 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. Show quote "David Portas" wrote: > 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 > -- > > 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. -- Show quoteHTH, SriSamp Email: sris***@gmail.com Blog: http://blogs.sqlxml.org/srinivassampath URL: http://www32.brinkster.com/srisamp "usamaalam" <usamaa***@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: > >> 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 >> -- >> >> 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. Show quote "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 powerful > formatting capabilities. > -- > HTH, > SriSamp > Email: sris***@gmail.com > Blog: http://blogs.sqlxml.org/srinivassampath > URL: http://www32.brinkster.com/srisamp > > "usamaalam" <usamaa***@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: > > > >> 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 > >> -- > >> > >> > > > 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. -- Show quoteHappy Holidays Dan Guzman SQL Server MVP "usamaalam" <usamaa***@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: > >> 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: sris***@gmail.com >> Blog: http://blogs.sqlxml.org/srinivassampath >> URL: http://www32.brinkster.com/srisamp >> >> "usamaalam" <usamaa***@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: >> > >> >> 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 >> >> -- >> >> >> >> >> >> >> 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/ On Fri, 23 Dec 2005 08:17:02 -0800, "ML" <M*@discussions.microsoft.com> wrote: in <61588BC5-7F5E-4BD4-A95E-7B6B24984***@microsoft.com> >Maybe you should consider using XML as a transport medium, instead of CSV. I don't understand that sort of reasoning. What makes a proprietary schema and>After all, this is the 21st century. :) bloated data better than the CSV format? --- Stefan Berglund Stefan Berglund wrote:
> On Fri, 23 Dec 2005 08:17:02 -0800, "ML" <M*@discussions.microsoft.com> wrote: For one thing an XML document can represent more complex data than a> in <61588BC5-7F5E-4BD4-A95E-7B6B24984***@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 "standard" CSV representation. Usually a CSV means a simple two dimensional list. XML on the other hand can represent much more complex structures with multiple levels of detail - something that isn't possible using what is conventionally understood as a CSV file. Another reason is that a CSV file has no standard equivalent of an XML schema document. A CSV is a semi-structured format at best, sometimes with and sometimes without column names. It isn't usually possible to share metadata in a CSV document. Thridly, XML supports escape sequences for special characters. Anyone who has imported delimited text files will be familiar with the problems inherent with delimiter characters in the data. Of course there are possible solutions to these limitations using CSV text files. It's just that there isn't a standard solution that has gained the industry acceptance and support of XML. -- David Portas SQL Server MVP -- On 23 Dec 2005 12:31:12 -0800, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote: in <1135369872.731357.296***@z14g2000cwz.googlegroups.com> Show quote >Stefan Berglund wrote: Thank you, sir for your most excellent (as usual) explanation. I'll stick with>> On Fri, 23 Dec 2005 08:17:02 -0800, "ML" <M*@discussions.microsoft.com> wrote: >> in <61588BC5-7F5E-4BD4-A95E-7B6B24984***@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 > >For one thing an XML document can represent more complex data than a >"standard" CSV representation. Usually a CSV means a simple two >dimensional list. XML on the other hand can represent much more complex >structures with multiple levels of detail - something that isn't >possible using what is conventionally understood as a CSV file. > >Another reason is that a CSV file has no standard equivalent of an XML >schema document. A CSV is a semi-structured format at best, sometimes >with and sometimes without column names. It isn't usually possible to >share metadata in a CSV document. > >Thridly, XML supports escape sequences for special characters. Anyone >who has imported delimited text files will be familiar with the >problems inherent with delimiter characters in the data. > >Of course there are possible solutions to these limitations using CSV >text files. It's just that there isn't a standard solution that has >gained the industry acceptance and support of XML. > >-- >David Portas >SQL Server MVP my tried and true methodologies until I'm forced by the situation to use other remedies. --- Stefan Berglund As David said there two major benefits in using XML as a transport media:
1) relationships; and 2) strong typing (as far as XML Schema allows). Maybe a third one: web services. Anyway, be sure to know help is available when you eventually decide on leaving the whirling world of flat files - right here in this newsgroup. ML --- http://milambda.blogspot.com/ Unfortunately in the real world, 99% of the time, you don't have the luxury
because you are interfacing with established systems that will not be changed or other companies that provide a fixed output with a take it or leave it attitude. In 2003-2004, I worked on a data warehouse project where we took data feeds from 5 different companies. No one offered XML. Most were flat ASCII text files in proprietary record layouts. One offered Excel because I think the person was using Excel to extract the data from their system for us. I agree that XML has advantages but as of yet, I have not encountered a company to offer it on an external data feed. I'm sure they are out there but I've always been suspicious that it's not as prevalent as Microsoft and other XML fans claim it to be. Just my two cents, Joe Show quote "ML" wrote: > As David said there two major benefits in using XML as a transport media: > 1) relationships; and > 2) strong typing (as far as XML Schema allows). > > Maybe a third one: web services. > > Anyway, be sure to know help is available when you eventually decide on > leaving the whirling world of flat files - right here in this newsgroup. > > > ML > > --- > http://milambda.blogspot.com/ Dinosaurs became extinct because they couldn't adapt to changes in environment.
I am (sadly) well aware of "well established" solutions. If they perform well they're OK, but if they could perform better they would be more than just OK. Evolution is not revolution - while the latter comes fast tearing down walls and burning bridges, the former is subtle and smooth. As a child of socialism who has seen it crumble and perish, I no longer believe revolution is the way to go. Evolution, on the other hand, may be "the one". I'm not saying *implement* XML, I'm saying *consider* implementing it. "Logic will get you from A to B. Imagination will take you everywhere." - Albert Einstein "No problem can be solved from the same level of consciousness that created it." - Albert Einstein ML --- http://milambda.blogspot.com/ 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 Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1135337208.989446.250540@g43g2000cwa.googlegroups.com... > 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 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: Show quote > 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. I agree with you people. I will change the data type. As far as export in
XML format is concerned, my client wants the export in CSV because some other system imports this data in CSV format which he is already using. Thanks a lot. Show quote "Trey Walpole" wrote: > 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. >  |
|||||||||||||||||||||||
Other interesting topics