price formatting

[andreas2410]

ok, i have a column of data type money and the data that is found in there is entered like this

1.1490
1.2340
1.0590
0.5990
0.5380

what i want to do is have it formatted as such
1149
1234
1059
0599
0538

ive tried using cast, convert, from money to varchar and simplying multiplying the field, replace and trunc but its not happening !!! any code help would be great!

thanks
andreas

[jai80]

hi andreas,

Try this :

declare @a varchar(10)
declare @b varchar(10)
set @a= convert(varchar(10),1.1490)
set @b=Replace(@a,'.','')
select left(@b,4)

Let me know if this has solved your problem. Gud Luck!

Cheers,
jai

ok, i have a column of data type money and the data that is found in there is entered like this

1.1490
1.2340
1.0590
0.5990
0.5380

what i want to do is have it formatted as such
1149
1234
1059
0599
0538

ive tried using cast, convert, from money to varchar and simplying multiplying the field, replace and trunc but its not happening !!! any code help would be great!

thanks
andreas

[almaz]

Recommended pattern is that any data formatting should be performed on the client side.

[almaz]

If you still want to format the data on server, use the following script:

declare @t table (price money)
insert @t values (1.1490)
insert @t values (1.2340)
insert @t values (1.0590)
insert @t values (0.5990)
insert @t values (0.5380)

select formatted_price = isnull(replicate('0', 4-len(price)), '') + price
from 
	(select price = convert(varchar(10), convert(int,price*1000)) 
	from @t) source

[andreas2410]

all of the code works perfectly fine, the thing is thought, that in dbo.vwCurrentFu elPrice there has a column CurrentPrice that i want to format in the way i described above. i want to be able to get it every time without having to manually enter the prices 1.1490 etc. thats how it gets put in if you get my drift.. thanks!

[andreas2410]

all of the code works perfectly fine, the thing is thought, that in dbo.vwCurrentFu elPrice there has a column CurrentPrice that i want to format in the way i described above. i want to be able to get it every time without having to manually enter the prices 1.1490 etc. thats how it gets put in if you get my drift.. thanks!

heres the offending code

declare @a varchar(10)
declare @b varchar(10)

SELECT DateEntered, dbo.Site.SiteCo de AS SiteCode, ('0' + DepartmentID) AS FuelCode, convert(varchar (10),CurrentPri ce)
FROM dbo.vwCurrentFu elPrice, dbo.Site
WHERE dbo.vwCurrentFu elPrice.SiteID = dbo.Site.SiteID

set @a = convert(varchar (10),dbo.vwCurr entFuelPrice.Cu rrentPrice)
set @b =Replace(@a,'.' ,'')
select left(@b,4)

that should help explain a bit about what im trying to achieve

[iburyak]

Try this:

[PHP]
select right('0000' + convert(varchar (10), convert(int, CurrentPrice * 1000)),4) from dbo.vwCurrentFu elPrice[/PHP]

[andreas2410]

legend! thanks heaps buddy

=)