Author |
Topic |
DesiGuju
Starting Member
12 Posts |
Posted - 2007-08-07 : 11:09:02
|
Good Morning Everyone,I have stored salary amount as varchar in my table and now i need to convert into currency format with "$" sign. I have wrote following funciton to convert the datatype'$'+ Convert(varchar,Convert(money,Salary),1)This converts 50000 into $50,000.00Now i need to get rid of decimals at the end so that $50,000.00 will display as $50,000What function/formatting do i need to use to get rid of decimals ?Thanks In Advance |
|
alanlambert
Starting Member
26 Posts |
Posted - 2007-08-07 : 11:17:38
|
Lookup CEILING and FLOOR functions in BOL.It is better to do the formatting (including adding the $ symbol) in the user interface rather that in the database.Alan |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-08-07 : 12:09:47
|
1) Always use proper data types to store your data. Never use VARCHAR to store a dollar amount, use MONEY.2) As mentioned, do not try to format results at your database layer; simply return the value from your database and format at your client. (i.e., web page, report, application, etc ...)- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-08-07 : 13:06:05
|
What Jeff said. :)But, here is one way:SELECT LEFT('$' + CONVERT(VARCHAR, CAST(Salary AS MONEY), 1), LEN('$'+ CONVERT(VARCHAR, CAST(Salary AS MONEY), 1)) - 3) |
|
|
DesiGuju
Starting Member
12 Posts |
Posted - 2007-08-07 : 13:06:23
|
Thanks for all the response,I'm only trying to format data at report level. I try using CEILING and FLOOR but it did not work.Any other suggestion ? |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-07 : 13:20:14
|
Did you try Lamprey's solution? If there are values after the decimal do you want to ROUND or FLOOR or leave them as is and only remove the decimals if its 0's?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
DesiGuju
Starting Member
12 Posts |
Posted - 2007-08-07 : 13:44:29
|
Thanks for all your helpLamprey solution work fine.Thanks |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-08-08 : 01:46:53
|
quote: Originally posted by DesiGuju Thanks for all the response,I'm only trying to format data at report level. I try using CEILING and FLOOR but it did not work.Any other suggestion ?
What is your reporting tool?A simple formula can do that"$"&to_text(Column) MadhivananFailing to plan is Planning to fail |
|
|
mlehiste
Starting Member
2 Posts |
Posted - 2007-08-22 : 12:30:39
|
quote: Originally posted by jsmith8858 1) Always use proper data types to store your data. Never use VARCHAR to store a dollar amount, use MONEY.2) As mentioned, do not try to format results at your database layer; simply return the value from your database and format at your client. (i.e., web page, report, application, etc ...)- Jeffhttp://weblogs.sqlteam.com/JeffS
Seriously - there needs to be formatting function in DB period - you cannot rely on some OTHER layers to do your work.1 - I need to store range2 - I need to store formatted result to the range fieldThanks for Lamprey for workaround, Thanks |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-08-22 : 12:33:56
|
>>Seriously - there needs to be formatting function in DB period - you cannot rely on some OTHER layers to do your work."Your work" in the database is to store, manipulate, and retrieve DATA. "Your work" in not formatting and presenting data, that is not what databases are designed for. The application that ultimately takes that data and displays it is in charge of formatting. They are *designed* and optimized to do that with all kinds of simple and easy formatting functions -- but they require raw DATA returned from a database to operate efficiently. If you try to format a nice DateTime value in a database into a particular format, you are NO LONGER RETURNING A DATETIME VALUE -- you are just returning a string of text. Your client now cannot do anything with that text other than display it, since it is no longer a true datetime value: it cannot re-format it, do math, sort it, and so on. It's really as simple as that. Does this not make sense? it's funny how people try to "make things easier" by trying to format at the database layer when it makes the work of BOTH layers actually much harder and the code much longer and the data less accurate and efficient. Oh well, some people like doing things the hard way.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
mlehiste
Starting Member
2 Posts |
Posted - 2007-08-22 : 12:47:11
|
It makes sense but there are times when you do need to do this sort of stuff in DB and since they already providing formatting for datetime - why not for money and numbers also |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-08-24 : 01:25:19
|
quote: Originally posted by mlehiste It makes sense but there are times when you do need to do this sort of stuff in DB and since they already providing formatting for datetime - why not for money and numbers also
As long as there is no front end, I wont mind MadhivananFailing to plan is Planning to fail |
|
|
chiraggoel
Starting Member
2 Posts |
Posted - 2009-11-27 : 00:55:35
|
'$'+ Convert(varchar,Convert(money,Salary),1)error Msg 117, Level 15, State 2, Procedure vwResults, Line 7The type name 'dbo.tblNN_Forecasts.dblProfitPer100' contains more than the maximum number of prefixes. The maximum is 1.is any body have solution |
|
|
vikky
Yak Posting Veteran
54 Posts |
Posted - 2009-11-27 : 01:14:36
|
Hi,This one easily solve our problem i think.SELECT PARSENAME('$'+ Convert(varchar,Convert(money,50000),1),2)Thanks,vikky. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-11-27 : 01:19:17
|
quote: Originally posted by chiraggoel '$'+ Convert(varchar,Convert(money,Salary),1)error Msg 117, Level 15, State 2, Procedure vwResults, Line 7The type name 'dbo.tblNN_Forecasts.dblProfitPer100' contains more than the maximum number of prefixes. The maximum is 1.is any body have solution
Start a new topic to get proper resultMadhivananFailing to plan is Planning to fail |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-11-27 : 01:20:52
|
quote: Originally posted by vikky Hi,This one easily solve our problem i think.SELECT PARSENAME('$'+ Convert(varchar,Convert(money,50000),1),2)Thanks,vikky.
That is not the problem. It is the problem in calling the objectMadhivananFailing to plan is Planning to fail |
|
|
|