Help with Money and Decimal datatype

Question

Post reply

Help with Money and Decimal datatype

karthik M

One Orange Chip

Points: 29139
More actions
November 18, 2008 at 3:15 am

#73102

Hi All,
I have the below scenario.
select convert(varchar(30),convert(decimal(18,4),234543242.7656422),1)
is showing
234543242.7656
Since i need the result set with comma seperated i used the below query.
select convert(varchar(30),convert(money,234543242.7656422),1)
is showing
234,543,242.77
But.... The problem is it is not showing 4 digits after the decimal.
My requirement is to display the result set with comma seperated as well as with 4 digits after the decimal point.
Inputs are welcome !
karthik

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Karthik

From BOL:

In the following table, the column on the left represents the style value for money or smallmoney conversion to character data.

Value Output

0 (default) No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

So using your data:

select convert(varchar(30),convert(money,234543242.7656422),0)

-- '234543242.77'

select convert(varchar(30),convert(money,234543242.7656422),1)

-- '234,543,242.77'

select convert(varchar(30),convert(money,234543242.7656422),2)

-- '234543242.7656'

So if you want '234,543,242.7656' as your output, you would have to code it yourself. A function would be ideal for this, and it could be easily written as follows:

Reverse the string

Find the position of the decimal point

Use STUFF to put commas in

Reverse the string back.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Johan Bijnens SSC Guru Points: 135554 More actions · Answer 2

presentation ....... should be handled by the front end application !

(and preferably client side)

Do not bother sqlserver with how you want things actually presented for the human eye.

One of the problems will eventually be that someone tries to hand it back to sqlserver in its formatted form, and you'll end up unformatting it yourself...

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Chris,

As you suggested, i have followed your steps.

select reverse(stuff(stuff(reverse(convert(varchar(30),convert(decimal(18,4),234543242.7656))),

charindex('.',reverse(convert(varchar(30),convert(decimal(18,4),234543242.7656))))+4,0,','),charindex('.',reverse(convert(varchar(30),convert(decimal(18,4),234543242.7656))))+8,0,','))

But if i have data something like that

34567.57454

213.34345

1.44

2312121221212.45

23232.45

I think my above query would fail. How should i handle these kind of situation ?

karthik

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

ALZDBA (11/18/2008)
presentation ....... should be handled by the front end application !
(and preferably client side)
Do not bother sqlserver with how you want things actually presented for the human eye.
One of the problems will eventually be that someone tries to hand it back to sqlserver in its formatted form, and you'll end up unformatting it yourself...

Although mostly true... not always true. For example, I don't use a front end to create a file with certain formatting requirements from data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135554 More actions · Answer 5

Jeff Moden (11/18/2008)
Although mostly true... not always true. For example, I don't use a front end to create a file with certain formatting requirements from data.

Arch ... formatted exports ... indeed .... very short sighted of me ... :blush:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

karthikeyan (11/18/2008)
Hi All,
I have the below scenario.
select convert(varchar(30),convert(decimal(18,4),234543242.7656422),1)
is showing
234543242.7656
Since i need the result set with comma seperated i used the below query.
select convert(varchar(30),convert(money,234543242.7656422),1)
is showing
234,543,242.77
But.... The problem is it is not showing 4 digits after the decimal.
My requirement is to display the result set with comma seperated as well as with 4 digits after the decimal point.
Inputs are welcome !

Ok... I gotta go with ALZDBA on this one... why are you tring to add commas in SQL Server? Do you have a GUI of any type?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 7

The answer is that you probably want to line up decimal points among all values with commas, anyway. So, do this...

[font="Courier New"]SELECT REPLACE(STUFF(STUFF(STUFF(STR(1234.7656422,16,4),9,0,','),6,0,','),3,0,','),' ,',' ')[/font]

Still want to know why you need to do this is SQL Server...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

Any feedback regarding my question, Karthick. Heh... "Inputs are welcome!" 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 9

Jeff,

Sorry ! a little late here..

Heh... "Inputs are welcome!"

🙂

Actually this query is scheduled as night job...what it will do ...it will run on every night and send the reports to the business users...Thats why i asked to format the result by using query itself....

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

karthikeyan (11/20/2008)
Jeff,
Sorry ! a little late here..
Heh... "Inputs are welcome!"
🙂
Actually this query is scheduled as night job...what it will do ...it will run on every night and send the reports to the business users...Thats why i asked to format the result by using query itself....

Hi Karthik

What client are you using for the reports? SSRS? Crystal?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 11

karthikeyan (11/20/2008)
...and send the reports to the business users...

Not quite what I was looking for... What type of report are you sending to the business users where you have to format the data in T-SQL? Are you just building text files to send them?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 12

Chris and Jeff,

Sorry a little late here.... Actually i wasn't able to open this one for the last 2 days....When i logged in it didn't open the pages....simply i saw only the login screen only...well, i dont know why...but now i am able to open...

We will be sending the reports in an excel sheet and send it via email.

karthik

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 13

Then you don't need to do the formatting in the file you send because the spreadsheet is gonna format it the way it want's. In fact, you adding commas and the like will probably mess Excel up. Don't do the formatting in SQL Server. Send plain data only.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)