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
 General SQL Server Forums
 New to SQL Server Programming
 Money Field Decimal Places
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

macca
Posting Yak Master

Ireland
146 Posts

Posted - 11/16/2007 :  10:51:27  Show Profile  Reply with Quote
I have a field in a database which is a datatype Money. When I run a select query the data is coming back with 4 decimal places like 100.0000 but I only want 2 decimal places like 100.00.

Anyone know how to get this?
macca

khtan
In (Som, Ni, Yak)

Singapore
17607 Posts

Posted - 11/16/2007 :  11:00:18  Show Profile  Reply with Quote
1. don't use money data type. money is 4 decimal places
2. use convert() or cast() to convert to decimal data type with 2 decimal places.


KH
Time is always against us

Go to Top of Page

macca
Posting Yak Master

Ireland
146 Posts

Posted - 11/16/2007 :  11:10:16  Show Profile  Reply with Quote
how would you use CAsT or Convert to do this, can you give an example please.

Thanks,
macca
Go to Top of Page

jhocutt
Constraint Violating Yak Guru

USA
385 Posts

Posted - 11/16/2007 :  11:12:34  Show Profile  Visit jhocutt's Homepage  Send jhocutt an AOL message  Reply with Quote
create table #tmp (
c1 money
)

insert into #tmp values (1.1234)
select c1, cast(c1 as numeric(10,2)), convert(numeric(10,2), c1)
from #tmp


"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Edited by - jhocutt on 11/16/2007 11:13:41
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17607 Posts

Posted - 11/16/2007 :  11:15:18  Show Profile  Reply with Quote
quote:
Originally posted by macca

how would you use CAsT or Convert to do this, can you give an example please.

Thanks,
macca


read the BOL


KH
Time is always against us

Go to Top of Page

jmanstream
Starting Member

2 Posts

Posted - 07/22/2011 :  06:12:53  Show Profile  Reply with Quote
Ummm... DICK

quote:
Originally posted by khtan

quote:
Originally posted by macca

how would you use CAsT or Convert to do this, can you give an example please.

Thanks,
macca


read the BOL


KH
Time is always against us



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 07/22/2011 :  06:18:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by jmanstream

Ummm... DICK

I hope that is an acronym for

Thank you so much for letting me know where I can find information about the easiest parts of SQL Server.
Not only will this help me in my future career, it will also let the volunteers on this forum spend their time on questions that have a real problem.



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/22/2011 :  06:48:35  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
quote:
Originally posted by SwePeso

quote:
Originally posted by jmanstream

Ummm... DICK

I hope that is an acronym for

Thank you so much for letting me know where I can find information about the easiest parts of SQL Server.
Not only will this help me in my future career, it will also let the volunteers on this forum spend their time on questions that have a real problem.



N 56°04'39.26"
E 12°55'05.63"




That's what I thought it stood for too... hmph

Corey

I Has Returned!!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/22/2011 :  09:31:20  Show Profile  Reply with Quote
quote:
Originally posted by jmanstream

Ummm... DICK

quote:
Originally posted by khtan

quote:
Originally posted by macca

how would you use CAsT or Convert to do this, can you give an example please.

Thanks,
macca


read the BOL


KH
Time is always against us







His post was a 1,000 times more helpful than yours with a direct link to the topic.

Why did you take the trouble to register on this website just to insult someone for no good reason?




CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 07/22/2011 :  09:53:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Seen it before. It's a prelude to spam.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

jmanstream
Starting Member

2 Posts

Posted - 08/26/2011 :  04:53:58  Show Profile  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by jmanstream

Ummm... DICK

quote:
Originally posted by khtan

quote:
Originally posted by macca

how would you use CAsT or Convert to do this, can you give an example please.

Thanks,
macca


read the BOL


KH
Time is always against us







His post was a 1,000 times more helpful than yours with a direct link to the topic.

Why did you take the trouble to register on this website just to insult someone for no good reason?




CODO ERGO SUM



No, I was actually looking for some related info myself, and I thought khtan's response was completely dismissive. macca asked if someone could provide an example and khtan's response was basically, "read the book".

He spent more time looking up the link to the BOL than it would have taken him to say what jhocutt did (the actual answer he asked for), after jhocutt had already answered the question. After all, isn't that the point of forums, in the first place? Everyone knows the BOL exists, but it's not always easy to decipher the answer from documentation. That's why we ask for examples from people who may have the exact answer.

I am a 15+ year software developer and DBA, but, occasionally, I still get stuck on a stupid issue that I look to the forums for help (or an example)... And if I posted that question, and someone posted a link to the docs, I would says the same... Gee thanks for pointing me to the documentation I've already read.

Maybe I'm beating a dead cat, but if the docs were all we ever needed, why would we have or need forums?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 08/26/2011 :  05:13:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Well, you can use forums if you don't bother to read about the product you are using. Hey, it works for your car right?
When you buy the car, just turn the key and drive away. It is as simple as that.

But what do you do when the lights are not working? Do you drive to the garage (forum) and ask what to do?
Or do you read the manual to see which fuse to replace yourself? Or investigate if you need to change the bulbs?
Or do you make someone at the garage tell you to replace a fuse or a bulb? It will be hard for the garage people to know what to do.
They may not know which model the car is.

You didn't tell us where and why you needed only two decimal places in the visualization.
The best bet may be to keep money as storage column and change something then presenting the data?
Or the best bet may be to redesign your application to use NUMERIC(17, 2)? And tell you what implications that give you...



N 56°04'39.26"
E 12°55'05.63"

Edited by - SwePeso on 08/26/2011 05:15:17
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/26/2011 :  06:06:30  Show Profile  Reply with Quote
Formatting should be done in the application layer.

If you absolutely must do it at the SQL end then there are formatting methods specific to the MONEY datatype:

CONVERT(varchar(20), MyMoney, 0)

will give you 2 d.p. or

CONVERT(varchar(20), MyMoney, 1)

if you want commas between the thousands too. There are other flavours - see documentation (and no, I'm not going to write them all out here ...)

Edited by - Kristen on 08/26/2011 06:08:01
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.16 seconds. Powered By: Snitz Forums 2000