|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help with formatting float data typesway. First allow me to list the fields involved, their data types, a sample value and the end result I desire. I'm trying to simulate an expression used in Access and would like to attempt the same sort of expression using SQL. The values for each field listed in the SQL datatypes are the same values used in the access expression. I would like some sound advice and not any criticism from some of the groups users, bottom line if you can help me please don't post a reply, email me if you have any questions. I have seen where instead of helping an individual out some like to bash them. I sincerely appreciate any help. The Access String function is similar to the REPLICATE function in SQL. Allow me to explain what the Access expression does, the expression formats the RateValue and if there is not deciaml at the end of RateValue it will format it and add "0" the amount of times dictated by the RateDecimalRounding value. If there is a decimal in RateValue, then the number of digits to the right a dictated by the value of RateDecimalRounding. Examples: RateValue = 6613054.5 RateDecimalRounding = 2 result: 6,613,054.50 RateValue = 31.53 RateDecimalRounding = 2 result: 31.53 RateValue = 500 RateDecimalRounding = 2 result: 500.00 RateValue = 1.0556 RateDecimalRounding = 4 result: 1.0556 RateValue = 1.0545 RateDecimalRounding = 6 result: 1.054500 ****************************************************************************** Access Expression: Format([RateValue],"#,###." & String([RateDecimalRounding],"0")) SQL datatypes RateValue float = 24000000000 RateDecimalRounding int = 2 (this field is used to replicate a value this many time, is this case 3) Here is the code I have so far cast(RateValue as varchar) + '.' + REPLICATE('0',RateDecimalRounding) I get 2.4e+008.00, what I would to achieve is 240,000,000.00 I hope I provided enogh information as to what i have and what I want t accomplish. Thanks, Frank Mmmmm
I am getting 24,000,000,000.00, i think you are missing a 0 declare @f float select @f =24000000000 select convert(varchar,convert(money,@f),3) Denis the SQL Menace http://sqlservercode.blogspot.com/ Frank Smith wrote: Show quote > I need some help with formatting a float data type to be displayed a certain > way. First allow me to list the fields involved, their data types, a sample > value and the end result I desire. I'm trying to simulate an expression used > in Access and would like to attempt the same sort of expression using SQL. > The values for each field listed in the SQL datatypes are the same values > used in the access expression. I would like some sound advice and not any > criticism from some of the groups users, bottom line if you can help me > please don't post a reply, email me if you have any questions. I have seen > where instead of helping an individual out some like to bash them. I > sincerely appreciate any help. The Access String function is similar to the > REPLICATE function in SQL. Allow me to explain what the Access expression > does, the expression formats the RateValue and if there is not deciaml at > the end of RateValue it will format it and add "0" the amount of times > dictated by the RateDecimalRounding value. If there is a decimal in > RateValue, then the number of digits to the right a dictated by the value of > RateDecimalRounding. > > Examples: > > RateValue = 6613054.5 > RateDecimalRounding = 2 > result: 6,613,054.50 > > RateValue = 31.53 > RateDecimalRounding = 2 > result: 31.53 > > RateValue = 500 > RateDecimalRounding = 2 > result: 500.00 > > RateValue = 1.0556 > RateDecimalRounding = 4 > result: 1.0556 > > RateValue = 1.0545 > RateDecimalRounding = 6 > result: 1.054500 > > > ****************************************************************************** > > Access Expression: > Format([RateValue],"#,###." & String([RateDecimalRounding],"0")) > > SQL datatypes > RateValue float = 24000000000 > RateDecimalRounding int = 2 (this field is used to replicate a value > this many time, is this case 3) > > Here is the code I have so far > > cast(RateValue as varchar) + '.' + REPLICATE('0',RateDecimalRounding) > > I get 2.4e+008.00, what I would to achieve is 240,000,000.00 > > I hope I provided enogh information as to what i have and what I want t > accomplish. > > > > Thanks, > > Frank Hi Frank,
Gosh, it really sounds like you have had bad experiences with other newsgroups. Welcome to the SQL Server newsgroups! We don't need to bash folks around -SQL Server does it enough! ;-) However, we are prone to question bad design, poor logic decisions, and inadequate question preparation. I, speaking for myself only, hope that the questions will help someone 'undo' or avoid design and/or logic decisions that will continue to haunt them in the future. Also, be aware, this is a volunteer help service, so there is NO one answer. Always take the suggestions with a 'grain of salt' and adapt them as necessary to your situation. It seems that you have adequately prepared your presentation, thanks for the examples and details -it helps a lot. This may help point you in a direction that works for you. (I've included some illustration code so that you can use Query Analyzer and see how this works. DECLARE @MyTable table ( RowID int IDENTITY , Rate varchar(25) ) INSERT INTO @MyTable VALUES ( '6613054.5' ) INSERT INTO @MyTable VALUES ( '31.53' ) INSERT INTO @MyTable VALUES ( '500' ) INSERT INTO @MyTable VALUES ( '1.0556' ) INSERT INTO @MyTable VALUES ( '1.0545' ) INSERT INTO @MyTable VALUES ( '24000000000' ) SELECT RowID , Rate , cast( Rate AS decimal(18,2 )) FROM @MyTable Good luck. -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Frank Smith" <pa***@yahoo.com> wrote in message news:ec5TDaTzGHA.3752@TK2MSFTNGP02.phx.gbl... >I need some help with formatting a float data type to be displayed a certain > way. First allow me to list the fields involved, their data types, a sample > value and the end result I desire. I'm trying to simulate an expression used > in Access and would like to attempt the same sort of expression using SQL. > The values for each field listed in the SQL datatypes are the same values > used in the access expression. I would like some sound advice and not any > criticism from some of the groups users, bottom line if you can help me > please don't post a reply, email me if you have any questions. I have seen > where instead of helping an individual out some like to bash them. I > sincerely appreciate any help. The Access String function is similar to the > REPLICATE function in SQL. Allow me to explain what the Access expression > does, the expression formats the RateValue and if there is not deciaml at > the end of RateValue it will format it and add "0" the amount of times > dictated by the RateDecimalRounding value. If there is a decimal in > RateValue, then the number of digits to the right a dictated by the value of > RateDecimalRounding. > > Examples: > > RateValue = 6613054.5 > RateDecimalRounding = 2 > result: 6,613,054.50 > > RateValue = 31.53 > RateDecimalRounding = 2 > result: 31.53 > > RateValue = 500 > RateDecimalRounding = 2 > result: 500.00 > > RateValue = 1.0556 > RateDecimalRounding = 4 > result: 1.0556 > > RateValue = 1.0545 > RateDecimalRounding = 6 > result: 1.054500 > > > ****************************************************************************** > > Access Expression: > Format([RateValue],"#,###." & String([RateDecimalRounding],"0")) > > SQL datatypes > RateValue float = 24000000000 > RateDecimalRounding int = 2 (this field is used to replicate a value > this many time, is this case 3) > > Here is the code I have so far > > cast(RateValue as varchar) + '.' + REPLICATE('0',RateDecimalRounding) > > I get 2.4e+008.00, what I would to achieve is 240,000,000.00 > > I hope I provided enogh information as to what i have and what I want t > accomplish. > > > > Thanks, > > Frank > > > > > Frank Smith (pa***@yahoo.com) writes:
> I'm trying to simulate an expression used in Access and would like to No matter you want criticism or not, I feel obliged to point out that> attempt the same sort of expression using SQL. The values for each field > listed in the SQL datatypes are the same values used in the access > expression. I would like some sound advice and not any criticism from > some of the groups users, this is not really the right thing to put into SQL Server. Keep in mind that SQL Server and Access are two quite different animals. Access is a front-end and a database engine in one piece. SQL Server is a database engine only. If you are used to Access, you may not have the feel for the distinction where different operations belong. Nice pretty formatting belongs in the client. A major reason for this is that the client can read the user's regional settings which SQL Server cannot. > Reading Books Online, it looks that this could work if you bounce the> Here is the code I have so far > > cast(RateValue as varchar) + '.' + REPLICATE('0',RateDecimalRounding) > > I get 2.4e+008.00, what I would to achieve is 240,000,000.00 value over the money data type: convert(varchar, convert(money, RateValue), 4) But it will not handle the number of decimals very well. SQL Server is weak on this type of operations. For reasons I explained above. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks to all who've replied, you have been much help in my decision to
resolve this issue. Again, thank you. Frank. Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns98316B0082A1Yazorman@127.0.0.1... > Frank Smith (pa***@yahoo.com) writes: >> I'm trying to simulate an expression used in Access and would like to >> attempt the same sort of expression using SQL. The values for each field >> listed in the SQL datatypes are the same values used in the access >> expression. I would like some sound advice and not any criticism from >> some of the groups users, > > No matter you want criticism or not, I feel obliged to point out that > this is not really the right thing to put into SQL Server. Keep in mind > that SQL Server and Access are two quite different animals. Access > is a front-end and a database engine in one piece. SQL Server is a > database engine only. > > If you are used to Access, you may not have the feel for the distinction > where different operations belong. Nice pretty formatting belongs in > the client. A major reason for this is that the client can read the > user's regional settings which SQL Server cannot. > >> >> Here is the code I have so far >> >> cast(RateValue as varchar) + '.' + >> REPLICATE('0',RateDecimalRounding) >> >> I get 2.4e+008.00, what I would to achieve is 240,000,000.00 > > Reading Books Online, it looks that this could work if you bounce the > value over the money data type: > > convert(varchar, convert(money, RateValue), 4) > > But it will not handle the number of decimals very well. > > SQL Server is weak on this type of operations. For reasons I explained > above. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics