Money and decimal places... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Money and decimal places...

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-03-21 : 04:43:51

Hi I have money value columns and I need to take just two decimal places.

How I can I update my money column to just hold 2 decimal places.

i.e
Money_column
1343.9455 i want as 1343.95

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 04:45:39

Maybe this?

UPDATE Table1
SET Money_Column = ROUND(Money_Column, 2)

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 04:51:41

Do you want to store data upto two decimal places or just format it to 2 decimal places while showing?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-03-21 : 04:54:27

Opps yep!

Thank you.

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-03-21 : 04:55:13

the round function is fine.

Many thanks

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-03-22 : 08:19:06

Oops

How can i not round the Money_column?

i.e. 123.568 to 123.56?

I cannot use substring as it is a money column.

I want to update the value so it stores with 2 decimal places and not only just replace it.

The Datatype needs to stay the same to money.

Thanks

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 09:15:16

quote:
Originally posted by Peso

Maybe this?

UPDATE Table1
SET Money_Column = ROUND(Money_Column, 2)

Peter Larsson
Helsingborg, Sweden

If i'm Correct... try:
Try using TRUNC(Money_Column, 2)

Vincent Fradnsen

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 09:16:34

Sorry i think i messed up there... doens't work

Vincent Fradnsen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 09:17:41

i am bit confuse. What do you really want ?
What do you mean "not round the Money column ?"

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 09:18:57

This is Correct SORRY...

C. Using ROUND to truncate
The following example uses two SELECT statements to demonstrate the difference between rounding and truncation. The first statement rounds the result. The second statement truncates the result.

Copy Code
SELECT ROUND(150.75, 0);
GO
SELECT ROUND(150.75, 0, 1);
GO

Here is the result set.

Copy Code
--------
151.00

(1 row(s) affected)

--------
150.00

(1 row(s) affected)

Vincent Fradnsen

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 09:20:33

KH I think truncated is what is required...

Vincent Fradnsen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 09:24:24

try this


declare @money_column money

select	@money_column = 1343.9455

select	[original] 	= @money_column, 
	[new]		= floor(@money_column * 100) / 100

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-03-22 : 09:36:18

Thanks

Although it's a bit weird for values ending with .00

VincentFrandsen
Starting Member

39 Posts

Posted - 2007-03-22 : 09:37:27

KH isnt that the same function as the round with the trunc option active?

... @Money_Column = 1343.9455
[origional] = @Money_Column
[new] = Round(@Money_Column,2,1)

... results
[new] = 1349.94

Vincent Fradnsen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 09:53:51

quote:
Originally posted by dnf999

Thanks

Although it's a bit weird for values ending with .00

you wanted to keep it as money datatype

quote:
The Datatype needs to stay the same to money.

if not just convert to decimal(18,2)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 09:56:01

quote:
Originally posted by VincentFrandsen

KH isnt that the same function as the round with the trunc option active?

... @Money_Column = 1343.9455
[origional] = @Money_Column
[new] = Round(@Money_Column,2,1)

... results
[new] = 1349.94

Vincent Fradnsen

yes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources