Limiting number of characters after decimal

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)

Limiting number of characters after decimal

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2009-01-07 : 08:46:38

I have results coming back from a query and I want to limit the number of characters after the decimal.

Example

2.000 I want to show as 2

I tried using round, but guess I am not reading it right.

Any help would be greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 08:49:25

CAST(Col1 AS INT) maybe?

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

dprichard
Yak Posting Veteran

94 Posts

Posted - 2009-01-07 : 09:06:23

PESO, thank you that worked for that column. I have one more column that shows as 17.990000.

I tried cast AS MONEY on that one, but could only get it to 17.9900. I need to have that one as 17.99. Any suggestions on that one?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 09:11:23

CAST(Col1 AS DECIMAL(15, 2)) maybe?

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

dprichard
Yak Posting Veteran

94 Posts

Posted - 2009-01-07 : 09:16:49

Thanks so much. I was just reading about using decimal with cast so I was almost there. Thanks again!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 09:21:47

You can also throw in ROUND, to make sure no values are rounded, just truncated.

DECLARE	@s DECIMAL(8, 4)

SET	@s = 17.9960

SELECT	@s,
	CAST(@s AS DECIMAL(8, 2)),
	CAST(ROUND(@s, 2, 1) AS DECIMAL(8, 2))

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources