Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Currency Format

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.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 - 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
Go to Top of Page

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 ...)

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

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)
Go to Top of Page

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 ?
Go to Top of Page

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/
Go to Top of Page

DesiGuju
Starting Member

12 Posts

Posted - 2007-08-07 : 13:44:29
Thanks for all your help

Lamprey solution work fine.

Thanks
Go to Top of Page

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)


Madhivanan

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

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 ...)

- 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

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.

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

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
Go to Top of Page

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

Madhivanan

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

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 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

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.
Go to Top of Page

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 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

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 object

Madhivanan

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

- Advertisement -