SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Currency Format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DesiGuju
Starting Member

12 Posts

Posted - 08/07/2007 :  11:09:02  Show Profile  Reply with Quote
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.00

Now i need to get rid of decimals at the end so that $50,000.00 will display as $50,000

What function/formatting do i need to use to get rid of decimals ?

Thanks In Advance


alanlambert
Starting Member

26 Posts

Posted - 08/07/2007 :  11:17:38  Show Profile  Reply with Quote
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

Edited by - alanlambert on 08/07/2007 11:18:43
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/07/2007 :  12:09:47  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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 ...)

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/07/2007 :  13:06:05  Show Profile  Reply with Quote
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)
Go to Top of Page

DesiGuju
Starting Member

12 Posts

Posted - 08/07/2007 :  13:06:23  Show Profile  Reply with Quote
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 ?
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 08/07/2007 :  13:20:14  Show Profile  Visit dinakar's Homepage  Reply with Quote
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/
Go to Top of Page

DesiGuju
Starting Member

12 Posts

Posted - 08/07/2007 :  13:44:29  Show Profile  Reply with Quote
Thanks for all your help

Lamprey solution work fine.

Thanks
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/08/2007 :  01:46:53  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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)


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mlehiste
Starting Member

2 Posts

Posted - 08/22/2007 :  12:30:39  Show Profile  Reply with Quote
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 ...)

- Jeff
http://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 range
2 - I need to store formatted result to the range field

Thanks for Lamprey for workaround,
Thanks

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/22/2007 :  12:33:56  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>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.

- Jeff
http://weblogs.sqlteam.com/JeffS

Edited by - jsmith8858 on 08/22/2007 12:39:07
Go to Top of Page

mlehiste
Starting Member

2 Posts

Posted - 08/22/2007 :  12:47:11  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/24/2007 :  01:25:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

chiraggoel
Starting Member

2 Posts

Posted - 11/27/2009 :  00:55:35  Show Profile  Reply with Quote
'$'+ Convert(varchar,Convert(money,Salary),1)

error Msg 117, Level 15, State 2, Procedure vwResults, Line 7
The type name 'dbo.tblNN_Forecasts.dblProfitPer100' contains more than the maximum number of prefixes. The maximum is 1.

is any body have solution
Go to Top of Page

vikky
Yak Posting Veteran

India
54 Posts

Posted - 11/27/2009 :  01:14:36  Show Profile  Reply with Quote
Hi,

This one easily solve our problem i think.

SELECT PARSENAME('$'+ Convert(varchar,Convert(money,50000),1),2)

Thanks,
vikky.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/27/2009 :  01:19:17  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by chiraggoel

'$'+ Convert(varchar,Convert(money,Salary),1)

error Msg 117, Level 15, State 2, Procedure vwResults, Line 7
The 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 result


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/27/2009 :  01:20:52  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 object

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000