SQL query output only need  two decimal places

Varshini S
Varshini S used Ask the Experts™
on
select  convert(varchar,totalamt ) as TotalAmount   from table1

Output:  18882.019

15882.050
8078.816

I need the output in the following format  (need only two decimal places). How do i achieve this ?
18882.01
15882.05
8078.81
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
SELECT CAST(totalamt AS DECIMAL(9,2)) AS TotalAmount
FROM table1

OR

SELECT ROUND(totalamt, 2) AS TotalAmount
FROM table1

Author

Commented:
Brain,
I need the output in varchar
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
I first question is "Why?" since this should be handled at the presentation layer but just wrap it in a CONVERT/CAST to varchar.

SELECT CAST(ROUND(totalamt, 2) AS VARCHAR) AS TotalAmount
FROM table1
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Author

Commented:
I am exporting the output to CSV.  So the column should be string.

Author

Commented:
I have used the below script. But the output still showing the third decimal 0

SELECT  CAST(ROUND(totalamt, 2) AS VARCHAR)   AS   TotalAmount
FROM table1


Output:
834.200
3015.380
Database Administrator
Top Expert 2005
Commented:
Use the CAST to DECIMAL instead, it also handles rounding.

SELECT CAST(CAST(totalamt AS DECIMAL(9,2)) AS VARCHAR) AS TotalAmount
FROM table1
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
if it is available to you (version SQL 2012 onward) try FORMAT()

e.g.

FORMAT(totalamt,'0.00')

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial