money/currency - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

money/currency

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-25 : 22:00:09

Hi all. Is there a way in SQL to convert the integer to currency format? just for example....
4000 convert to 4,000
1312500 convert to 1,312,500
30000 convert to 30,000

Most of you will say "Do it in your front end"... but the problem is I don't know how to do it in my Report(Business Intelligence Project). If anyone of you knows, tell me please... Thanks. :)

-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 22:03:51

[code]
declare @tbl table
(
i int
)

insert into @tbl
select 4000 union all
select 1312500 union all
select 30000

select convert(varchar(20), convert(money, i), 1)
from @tbl
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 22:09:20

and to remove the decimal points after that

replace(convert(varchar(20), convert(money, i), 1), '.00', '')

BTW, i also don't know how to do it in Business Intelligence Project

. In fact, i don't know anything about Business Intelligence Project.

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-25 : 22:24:37

thanks KH.
LMAO, me too still learning on it. well, it is VERY EASY to use and manage in terms of design, groupings, etc... what i don't like is, it has many bugs... SP2 has bug on preview, it displays black background. lol. And what i hated most is if I have 4 pages report, the first page is OK, 2nd and 3rd will give you a blank body, and the 4th is the continuation of the 1st page... grrrrr don't know how to solve it.

-Ron-

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-25 : 22:55:30

Opsss KH please help again...
my expected result is 4,000.00 with 2 decimal places.

thanks
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 22:56:40

use the code in my 1st post

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-25 : 23:00:41

ops sorry didn't notice your first post.

Edit .:. Can I convert it to integer instead of varchar? I am doing arithmetic operation in my report.

thanks
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 23:01:30

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-25 : 23:14:29

quote:
Originally posted by cwtriguns2002

ops sorry didn't notice your first post.

Edit .:. Can I convert it to integer instead of varchar? I am doing arithmetic operation in my report.

thanks
-Ron-

That's the reason why this should be done in the front end

You can't have integer value with comma , like 4,000. This will be a varchar string. Will looks like you have to do the formatting in the front end

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-25 : 23:19:22

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-26 : 01:41:21

KH, Just want to tell you that i solved it.

Business Intelligence Project has no build in function for this, what i did is i created a function(convert to money) in REPORT CODE then I call it. YEWEY...

thanks
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-26 : 01:51:09

quote:
Originally posted by cwtriguns2002

KH, Just want to tell you that i solved it.
Business Intelligence Project has no build in function for this, what i did is i created a function(convert to money) in REPORT CODE then I call it. YEWEY...

thanks
-Ron-

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-26 : 08:53:32

if this is a Crystal report, just right-click on the field on the report and set the number format options. It's very easy. From there, you have all kinds of things you can choose from. Formatting within sql server means that VARCAHR data is always returned in that column, and crystal or any other reporting tool will have trouble formatting/sorting it since it is no longer a numeric value.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources