| Author |
Topic |
|
macca
Posting Yak Master
146 Posts |
 Posted - 2007-11-16 : 10:51:27
|
| 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)
17689 Posts |
Posted - 2007-11-16 : 11:00:18
|
1. don't use money data type. money is 4 decimal places2. use convert() or cast() to convert to decimal data type with 2 decimal places.
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
macca
Posting Yak Master
146 Posts |
Posted - 2007-11-16 : 11:10:16
|
| how would you use CAsT or Convert to do this, can you give an example please.Thanks,macca |
|
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2007-11-16 : 11:12:34
|
| 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 |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-16 : 11:15:18
|
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[spoiler]Time is always against us[/spoiler] |
|
|
|
jmanstream
Starting Member
2 Posts |
Posted - 2011-07-22 : 06:12:53
|
Ummm... DICKquote:
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[spoiler]Time is always against us[/spoiler]
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-22 : 06:18:40
|
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" |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-07-22 : 06:48:35
|
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!! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-07-22 : 09:31:20
|
quote:
Originally posted by jmanstream
Ummm... DICKquote:
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[spoiler]Time is always against us[/spoiler]
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-22 : 09:53:56
|
Seen it before. It's a prelude to spam.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
jmanstream
Starting Member
2 Posts |
Posted - 2011-08-26 : 04:53:58
|
quote:
Originally posted by Michael Valentine Jones
quote:
Originally posted by jmanstream
Ummm... DICKquote:
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[spoiler]Time is always against us[/spoiler]
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? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-08-26 : 05:13:33
|
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" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-26 : 06:06:30
|
| 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 ...) |
|
|
|
|
Money Field Decimal Places