| Author |
Topic |
|
bradm
Starting Member
4 Posts |
 Posted - 2008-08-05 : 14:48:48
|
| I am an IT Admin for a small company, and we are running SQL Server 2005 for our main DB. There is a table called Parts that has the costs and prices for different parts. It's using the money data type, which, as I understand it, always puts 4 decimal places. I need to somehow convert a price such as $0.7980 to $0.8000 or 0.80. Is there a rounding function I can use?ThanksBrad MayIT Administrator |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-05 : 15:13:05
|
| how about ROUND:declare @m money; set @m = 0.7980 select round(@m, 2)output:---------------------0.80EDIT:It is better to perform any formatting of data with the presentation layer. If you are using a front end application format it there.Be One with the OptimizerTG |
 |
|
|
bradm
Starting Member
4 Posts |
Posted - 2008-08-06 : 09:32:15
|
| I have never used SQL Server 2005 before. I don't even know where to start when editing a column in a table. Can someone tell me exactly how to do what's in the above post?ThanksBrad MayIT Administrator |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-06 : 09:48:56
|
| Brad, before we start explaining how to change the datatype of the column, or update the values to be less precise, rounded values, why don't you expain what task you have been assigned. If it is related to making a report (or output) appear a certain way then you really don't want to be messing with the data content. The reason I ask is that changing the datatype or even the stored values can have unintended ramifications and if you don't know how to do it then it is likely you don't need to do it.Be One with the OptimizerTG |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
bradm
Starting Member
4 Posts |
Posted - 2008-08-06 : 11:41:45
|
| To TG:Ok, no problem. I'm the only IT guy in the company. So, yes I have the access and the need to change the data in the tables. Our database is accessed by a VB front end program that really just displays and updates values through text boxes for the most part. Anyway, there will not be any adverse effects if I change 0.798 to 0.8000 or 0.80 (unless there's something in SQL Server 05 that will be affected). That VB program is the only program that uses the database...well, we run Access reports on it too, but won't hurt that either. So yeah, I'm the only IT guy, so I've assigned this task to myself.To X002548:I don't understand your question.Brad MayIT Administrator |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-06 : 12:44:17
|
>>So, yes I have the access and the need to change the data in the tablesI still don't know why you think you need to do this rather than round the values in the VB application code (or even round the values in database calls issued by the VB application code.But to answer your question - run this in a query window then use Books Online to understand the commands.create table #t (m money)insert #t (m) values (0.7980)goselect m [raw], round(m, 2) [rounded], round(m, 2) * 2 [calcOffRounded] from #tgoalter table #t alter column m numeric(17,2)goselect m from #tgodrop table #t Be One with the OptimizerTG |
|
|
|
bradm
Starting Member
4 Posts |
Posted - 2008-08-06 : 14:54:21
|
| Thanks for the help. I really appreciate it. I will look further into the syntax and make sure this is what I want to do.Brad May |
|
|
|
|
money data type formatting