dynamic numeric format - 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)

dynamic numeric format

Author

Topic

nice123ej
Starting Member

48 Posts

Posted - 2008-03-03 : 02:33:02

i want to do something like this
i know it does not work this way, i want other way to do this

declare @a int
set @a = 2
select convert(numeric(18,@a), 76.3823)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-03 : 03:05:44

Why do you need this?

declare @a int, @sql varchar(8000)
set @a = 2
set @sql='select convert(numeric(18,'+cast(@a as varchar(5))+'), 76.3823)'
EXEC(@sql)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-03 : 03:43:31

and without dynamic sql

declare @a int
set @a = 2

select round(76.3823, @a)

it still has trailing zeros, but the value itself is rounded according to specifications.

E 12°55'05.25"
N 56°04'39.16"

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-03-03 : 19:08:18

If you have a "few" values for @a: (e.g. 2, 3, 4)

declare @a int
set @a = 2
.
.
.
if @a = 2
select convert(numeric(18,2), 76.3823)
else if @a = 3
select convert(numeric(18,3), 76.3823)
else
select convert(numeric(18,4), 76.3823)

=======================================
Society is like a stew. If you don't keep it stirred up you get a lot of scum on the top. -Edward Abbey, naturalist and author (1927-1989)

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-06 : 23:54:14

This will do it without the trailing zeros...

DECLARE @d INT
SET @d = 1

SELECT STR(123456.789,10,@d)

The real question should be, why aren't you doing this formatting in the GUI>

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-07 : 03:10:39

quote:
Originally posted by Jeff Moden

This will do it without the trailing zeros...

DECLARE @d INT
SET @d = 1

SELECT STR(123456.789,10,@d)

The real question should be, why aren't you doing this formatting in the GUI>

--Jeff Moden

Wont you have leading spaces?

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-07 : 07:19:14

nice123ej -- what are you trying to do here -- round a value for further T-SQL calculations, or format a value to make it look nice? If you are rounding, use math to do this, if you are formatting, do this at your presentation layer.

- Jeff
http://weblogs.sqlteam.com/JeffS

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-07 : 18:53:47

quote:
Originally posted by madhivanan

quote:
Originally posted by Jeff Moden

This will do it without the trailing zeros...

DECLARE @d INT
SET @d = 1

SELECT STR(123456.789,10,@d)

The real question should be, why aren't you doing this formatting in the GUI>

--Jeff Moden

Wont you have leading spaces?

Madhivanan

Failing to plan is Planning to fail

Yes... so add LTRIM.

Jeff S is echoing what I said though... if it's for formatting, and I think it is because folks are worried about trailing zeros, and if a GUI is involved, then do the formatting in the GUI.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources