Cast as Money

Question

Cast as Money

Sean Grebey-262535

SSCommitted

Points: 1724
More actions
January 3, 2012 at 7:48 am

#249124

I am trying to divide a column by 26 and cast the result as Money for an Update/Select SQL Query. I'm just figuring out the select part first, but I notice when I run it I'm getting 4 decimal places when I do:
CAST((E.[MED Acct# - Participant's Annual Election]/26) as Money)
e.g. for 2500 I am getting 96.1538, when what I need and what I thought I'd get is 96.16
What will happen when I try to update a money column with 96.1538. Will it get 96.15 or 96.16?
Or alternately does anybody have a better way to do this?
Thanks.
Sean

Viewing 9 posts - 1 through 8 (of 8 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Sean Grebey-262535 (1/3/2012)
I am trying to divide a column by 26 and cast the result as Money for an Update/Select SQL Query. I'm just figuring out the select part first, but I notice when I run it I'm getting 4 decimal places when I do:
CAST((E.[MED Acct# - Participant's Annual Election]/26) as Money)
e.g. for 2500 I am getting 96.1538, when what I need and what I thought I'd get is 96.16
What will happen when I try to update a money column with 96.1538. Will it get 96.15 or 96.16?
Or alternately does anybody have a better way to do this?
Thanks.
Sean

You are getting 4 decimal places with the money datatype because that is the precision of that datatype.

declare @Money money

set @Money = 96.1538

select @Money

http://msdn.microsoft.com/en-us/library/ms179882.aspx

Have you tried to see what would happen?

create table #MyTable

(

col1 money

)

insert #MyTable select 1.25

select * from #MyTable

update #MyTable set col1 = 96.1538

select * from #MyTable

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 2

Yeah, it puts 4 digits in. That seems a bit silly. How can you have $96.1538? I need it to represent actual money payments. Any thoughts on how I shorten it to 2 digits always rounded up? If i just cast it as a decimal it rounds normal, I need it to round up. Thanks.

Sean

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Sean Grebey-262535 (1/3/2012)
Yeah, it puts 4 digits in. That seems a bit silly. How can you have $96.1538? I need it to represent actual money payments. Any thoughts on how I shorten it to 2 digits always rounded up? Thanks.
Sean

Ever done anything with interest calculations? 😉 The financial industry agreed that 4 decimal places is close enough which is why money is accurate to 4 decimal places.

If you don't want 4 decimal places than the money datatype is not the correct datatype. Something like decimal (9,2)??? I don't know what scale is appropriate for your situation.

Take the example from my previous post. This will

select ROUND(col1, 2) from #MyTable

update #MyTable set col1 = 96.1568

select ROUND(col1, 2) from #MyTable

Hope that helps.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 4

Sean Grebey-262535

SSCommitted

Points: 1724

January 3, 2012 at 8:20 am

#1427764

That will just round. How do I round up? Thanks!

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 5

(Ceiling(CAST((E.[MED Acct# - Participant's Annual Election]/26) as Money) * 100)/100)

Seemed to do it. Thanks for the help.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Sean Grebey-262535 (1/3/2012)
That will just round. How do I round up? Thanks!

Something like this?

select cast(ROUND(col1 + .005, 2) as decimal(9,2)), * from #MyTable

Of course rounding up is not actually rounding. It is a ceiling function but simply rounding with an additional .005 does the same thing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Sean Grebey-262535 (1/3/2012)
(Ceiling(CAST((E.[MED Acct# - Participant's Annual Election]/26) as Money) * 100)/100)
Seemed to do it. Thanks for the help.

This would do it too. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 8

Hi

SELECT round(cast(((medacct# - ParticipantsAnnualElection)/26)as money),0) as Money

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com