convert to the nearest tens,hundreds and thousands

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 2008 Forums

Transact-SQL (2008)

convert to the nearest tens,hundreds and thousands

Author

Topic

ntswakisto
Starting Member

33 Posts

Posted - 2009-11-23 : 02:42:50

Hi

Can anyone help!!
I have this 3287585,62497854 ...and would like to convert to this 3,287,586 in my select result...

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-23 : 03:00:03

SELECT ROUND(YourValueHere, -1), ROUND(YourValueHere, -3)

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-23 : 03:01:47

[code]SELECT NULL, 3287585.62497854 UNION ALL
SELECT 0, ROUND(3287585.62497854, 0) UNION ALL
SELECT -1, ROUND(3287585.62497854, -1) UNION ALL
SELECT -2, ROUND(3287585.62497854, -2) UNION ALL
SELECT -3, ROUND(3287585.62497854, -3)[/code]

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

ntswakisto
Starting Member

33 Posts

Posted - 2009-11-23 : 03:11:00

Hi peso

thanks for ,,but round doesn't give me the result that I want...
ROUND(3287585.62497854, 0) gives me 328759 I want to separate tens,hundreds and thousands with commas......

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-23 : 04:04:22

quote:
Originally posted by ntswakisto

Hi peso

thanks for ,,but round doesn't give me the result that I want...
ROUND(3287585.62497854, 0) gives me 328759 I want to separate tens,hundreds and thousands with commas......

Where do you want to show data?
If you use front end application, do formation there

or

select convert(varchar(30),cast(3287585 as money),1)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-23 : 04:35:40

quote:
Originally posted by ntswakisto

Hi peso

thanks for ,,but round doesn't give me the result that I want...
ROUND(3287585.62497854, 0) gives me 328759 I want to separate tens,hundreds and thousands with commas......

Zero gives me 3287586.00000000, not 328759

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

ntswakisto
Starting Member

33 Posts

Posted - 2009-11-25 : 06:01:31

i want to get this results

3,287,586

ntswakisto
Starting Member

33 Posts

Posted - 2009-11-25 : 06:18:09

quote:
Originally posted by madhivanan

quote:
Originally posted by ntswakisto

Hi peso

thanks for ,,but round doesn't give me the result that I want...
ROUND(3287585.62497854, 0) gives me 328759 I want to separate tens,hundreds and thousands with commas......

Where do you want to show data?
If you use front end application, do formation there

or

select convert(varchar(30),cast(3287585 as money),1)

Madhivanan

Failing to plan is Planning to fail

Thanks
Madhivanan

It's working......

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-25 : 07:31:25

But keep in mind that if you use front end application, you should format the amount there

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-25 : 10:39:23

What OP want is NOT conversion. It is formatting.
See answer posted 11/23/2009 : 04:04:22

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 01:51:42

quote:
Originally posted by Peso

What OP want is NOT conversion. It is formatting.
See answer posted 11/23/2009 : 04:04:22

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

Yes that was why my previous reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources