Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

format results into currency

Status
Not open for further replies.

kevgais

Technical User
May 20, 2005
30
GB
Hi Guys,

A complete noob here so be gentle.

I have this statement (compiled from examples I have found on the web) which works fine, however I want the 'totalsales' column to be formatted into a currency format.

set dateformat dmy
SELECT convert(varchar,ChitClosedDateTime,103) ChitClosedDateTime, EmployeeNumber, SUM (ChitTotalAmount) as TotalSales
FROM COReMicrosChitInterface
where EmployeeNumber between 1 and 200
and ChitClosedDateTime between convert(datetime, '11/10/2006 00:00') and convert(datetime,'12/10/2006 23:59')
GROUP BY convert(varchar,ChitClosedDateTime,103), EmployeeNumber
order by totalsales desc


I have found this bit of code

SELECT '£' + Convert(Varchar, ChitTotalAmount) AS totalsales

but am unsure of where to put it.

any assistance greatfully recivied.

thanks
Kev
 
Your select statement should look something like this.
Code:
set dateformat dmy
SELECT convert(varchar,ChitClosedDateTime,103) ChitClosedDateTime, EmployeeNumber, '£' + Convert(Varchar, SUM (ChitTotalAmount)) as TotalSales
FROM COReMicrosChitInterface
where EmployeeNumber between 1 and 200
        and ChitClosedDateTime between convert(datetime, '11/10/2006 00:00') and convert(datetime,'12/10/2006 23:59')
GROUP BY convert(varchar,ChitClosedDateTime,103), EmployeeNumber
order by totalsales desc

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
thank you so much for your help.

it seems now though that the order by isn't working correctly as shown below:

this is suppose to be sorted in desc order

any ideas?

thanks again.

£97.20
£94.80
£91.50
£885.80
£85.15
£84.75
£84.55
£84.00
£83.30
£8.75
£8.55
£78.40
£77.00
£75.00
£74.65
£74.05
£711.10
£709.05
£69.75
£64.15
£614.15
£604.80
£60.60
 
It is sorting as you requested.

You code has changed the datatype to a text type and so the sort is text based, left to right of the characters in each string.

Hope this helps.

[vampire][bat]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top