formatting numeric values - 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)

formatting numeric values

Author

Topic

missMac
Posting Yak Master

124 Posts

Posted - 2009-04-24 : 09:15:31

hello guys,
we normally do our formatting in our .net frontend.

but wondering how do I format numeric values in mssql

ie to represent 100000000.00

as

100,000,000.00

tnx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 09:17:39

See Convert function in Books Online.

CONVERT(VARCHAR(10), CAST(Col1 AS MONEY), 1)

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-24 : 11:03:11

<<
we normally do our formatting in our .net frontend.
>>

Continue doing there

Madhivanan

Failing to plan is Planning to fail

missMac
Posting Yak Master

124 Posts

Posted - 2009-04-24 : 12:30:26

quote:
Originally posted by madhivanan

<<
we normally do our formatting in our .net frontend.
>>

Continue doing there

Madhivanan

Failing to plan is Planning to fail

did you read my post or just trying to get your post count up ???

LOL

missMac
Posting Yak Master

124 Posts

Posted - 2009-04-24 : 12:35:56

quote:
Originally posted by Peso

See Convert function in Books Online.

CONVERT(VARCHAR(10), CAST(Col1 AS MONEY), 1)

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

Thanks Peter.

do appreciate this.

missMac
Posting Yak Master

124 Posts

Posted - 2009-04-24 : 13:05:19

One question though, when i try to convert 1million. I get this error

Msg 234, Level 16, State 2, Procedure sp_prices_calculator, Line 20
There is insufficient result space to convert a money value to varchar.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-24 : 13:24:49

VARCHAR(10) is too small to hold the converted string. Try using VARCHAR(25) instead. (I think 25 is the max length for a converted money value with a format style of 1.)

missMac
Posting Yak Master

124 Posts

Posted - 2009-04-24 : 17:55:28

quote:
Originally posted by Lamprey

VARCHAR(10) is too small to hold the converted string. Try using VARCHAR(25) instead. (I think 25 is the max length for a converted money value with a format style of 1.)

thanks it worked. But i noticed it doesnt go beyond 1million.

mm

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-24 : 19:27:27

Are you doing some other conversion that is limited the value to 1 million? This works for me:

DECLARE @Cash NUMERIC
SET @Cash = 10000000000.00

SELECT CONVERT(VARCHAR(25), CAST(@Cash AS MONEY), 1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-25 : 02:21:44

quote:
Originally posted by missMac

quote:
Originally posted by madhivanan

<<
we normally do our formatting in our .net frontend.
>>

Continue doing there

Madhivanan

Failing to plan is Planning to fail

did you read my post or just trying to get your post count up ???

LOL

You cannot do arithmetic operations with Formatted values. Thats why I suggested you to resturn data as such to client and continue doing the formation in .NET as you were already doing

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources