Author |
Topic |
jamie
Aged Yak Warrior
542 Posts |
Posted - 2008-05-23 : 06:01:50
|
hi, how can I convert a float to a char so I can do:'£' + convert(char,amount) AS [money]When I do the above the number is like 1.07848e+006 rather than 1078480thans for any help.jamie |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-05-23 : 06:04:53
|
[code]Select '£' + convert(varchar(10),convert(numeric(12,2),amount)) AS [money][/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2008-05-23 : 06:10:10
|
thats great thanks ! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-23 : 06:21:06
|
[code]declare @f floatset @f = 1078480select '£' + convert(char,@f) AS jamie, '£' + convert(varchar(10), convert(numeric(12, 2), @f)) AS harsh, '£' + LTRIM(STR(@f, 20, 2)) AS PesoNoDecimals, '£' + LTRIM(STR(@f, 20, 0)) AS PesoWithDecimals[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2008-05-23 : 08:14:31
|
peso, thats cool aswell.thank you.ps, is it possible to format numbers with commas in SQL ?eg, 2400 = 2,400 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-23 : 08:19:34
|
Yes, have a look at CONVERT function in Books Online. E 12°55'05.25"N 56°04'39.16" |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2008-06-03 : 07:23:34
|
hi, I cannot find how to do this ? can anyone help ? eg, convert 2400 to 2,400 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-06-03 : 07:26:33
|
Really?select convert(varchar(10), cast(2400 as money), 1) Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-03 : 07:31:52
|
quote: Originally posted by jamie hi, I cannot find how to do this ? can anyone help ? eg, convert 2400 to 2,400
What you're trying to do here are formatting of data. Its always better to do this type of formatting at your front end application.and if you want to do it in sql do like thisSELECT CONVERT(varchar(10),yourmoneyvalue,1) |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2008-06-03 : 07:45:11
|
ah, I need to convert it to money !! thank you thank you |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2008-06-03 : 07:58:04
|
This added decmial places to the end, eg, 2,400.00. how can I just show 2,400 ?Is this possible with the money datatype ? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-03 : 08:00:36
|
quote: Originally posted by jamie This added decmial places to the end, eg, 2,400.00. how can I just show 2,400 ?Is this possible with the money datatype ?
use LEFT() function |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-03 : 08:02:38
|
You want to go from "1078480" to "£1,078,480" ? E 12°55'05.25"N 56°04'39.16" |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2008-06-03 : 08:04:26
|
that is correct peso |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-03 : 08:18:56
|
And there is no other way to accomplish this in your report engine or front-end application? E 12°55'05.25"N 56°04'39.16" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-03 : 08:21:31
|
[code]DECLARE @f FLOATSET @f = 1078489SELECT LEFT('£' + CONVERT(VARCHAR(20), CAST(@f AS MONEY), 1), LEN(@f) - 2)[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2008-06-03 : 08:27:36
|
hi, I just this second worked it out :select left(ltrim(convert(char(10),cast('20000'as money),1)),LEN(ltrim(convert(char(10),cast('20000'as money),1)))-3)a pain though ! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-03 : 08:51:52
|
Yes, it seems simpler to use than the suggestion made 06/03/2008 : 08:21:31 E 12°55'05.25"N 56°04'39.16" |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2008-06-04 : 04:51:48
|
hi peso, thank you for your suggestion. I didn't see that before I worked it out, yours is more readable !one last question on formatting numbers / money. how can I format 5300000 as 5.3?thank you for all your helpJamie |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-04 : 05:51:45
|
Divide by 100,000 and round to 1 decimal? E 12°55'05.25"N 56°04'39.16" |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2008-06-04 : 06:01:29
|
ok, cheers, I'll try that. |
|
|
Next Page
|