Author |
Topic |
sz1
Aged Yak Warrior
555 Posts |
Posted - 2012-03-26 : 07:35:43
|
I know we can do a for dates.Convert(varchar, Mydate,100) as 'MyDateFormat'How do I do the same for money to currency so, to change the below prices to $00.00 format:StartDate StandardCost ListPrice totalcost ProfitJul 1 2003 12:00AM 0.8565 2.29 3.1465 1.4335 |
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2012-03-26 : 07:41:27
|
this seemed to work '$'+Convert(varchar,Convert(money,StandardCost),1)any better ways please let me know. I know its better to do this in the front end but if a client needs a quick export this is handy to copy straight to Excel. |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2012-03-26 : 07:56:34
|
How do I do the same for this including a SUMSo,'£' Convert(varchar, Convert(money(SUM(ListPrice + StandardCost),1)) as totalcost,?Thanks |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2012-03-26 : 11:05:13
|
Sorted it, you dont need the SUM function. Use AdventureWorks Go Select Convert(varchar,SellStartDate,100) as 'StartDate', -- convert to date format '£' + Convert(varchar,Convert(money,StandardCost),1) as StandardCost, -- convert to currency '£' + Convert(varchar,Convert(money,ListPrice),1) as ListPrice, '£' + Convert(varchar,Convert(money,ListPrice + StandardCost),1) as totalcost, '£' + Convert(varchar,Convert(money,ListPrice - StandardCost),1) as Profit from Production.Product Where StandardCost >0 Group by ListPrice, StandardCost, SellStartDate Order by StandardCostRESULTStartDate StandardCost ListPrice totalcost ProfitJul 1 2003 12:00AM £0.86 £2.29 £3.15 £1.43Jun 1 1998 12:00AM £1,059.31 £1,431.50 £2,490.81 £372.19Jul 1 2003 12:00AM £1,082.51 £1,700.99 £2,783.50 £618.48Jul 1 2002 12:00AM £1,251.98 £2,294.99 £3,546.97 £1,043.01Jul 1 2002 12:00AM £1,265.62 £2,319.99 £3,585.61 £1,054.37Jul 1 2003 12:00AM £1,481.94 £2,384.07 £3,866.01 £902.13 |
|
|
maryjohnson
Starting Member
3 Posts |
Posted - 2012-04-26 : 08:48:44
|
for details of all currency please visit,,http://www.currency.com.pk/uae-gold-rate-dubai-exchange-rate.html |
|
|
|
|
|