Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 8th, 2004, 10:34 AM
Authorized User
 
Join Date: Apr 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL - removing trailing zeros

I have the following sql:

select upper(ac_activity_descr)
,ws_score
,ws_impact
,ws_target
,round((ws_score*(ws_impact/ws_target)),2)
, ws_activity_no
from web_scorecard, activity

where ws_quarter='q1 2003' --variable
and ws_engineer='keyefran' --variable
and activity.ac_activity_no = ws_activity_no

order by ws_activity_no

The output is:


CASES PER WEEK 2.34 60.00 3.00 46.80000000000 1
% CLOSED 80.77 50.00 50.00 80.77000000000 2
% CLOSED 24 42.86 15.00 50.00 12.86000000000 3
% REOPEN .00 -10.00 7.00 .00000000000 4
AVE DAYS STATUS 2.10 -40.00 2.00 -42.00000000000 5
AVE DAYS RESOLVE 4.17 -10.00 7.00 -5.96000000000 6
IA 80.00 70.00 80.00 70.00000000000 7
GAP 2.00 -35.00 2.00 -35.00000000000 8

In column 5, you can see there are many trailing zeros. I want to only view up to 2 decimal places. if i use the round function it rounds to 2 decimal places but does not remove the other trailing zeros - how can i do this?

Thanks in advance, Luke


Yeeeeeha cowboy!
__________________
Yeeeeeha cowboy!
 
Old April 8th, 2004, 05:25 PM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

First check the data type for that column.

you can use cast to change the field type

SELECT CAST(ws_score AS int)

 
Old April 8th, 2004, 10:22 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Or if you want to keep atleast 2 decimal places and remove off the others, you can use

select cast(150.790000000 as decimal(10,2))

Cheers!

-Vijay G
 
Old April 12th, 2004, 05:53 AM
Authorized User
 
Join Date: Apr 2004
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply, sorry about the late 'Thankyou' but its been Easter!

I will give this a go and see if it works.

Many thanks, Luke

Yeeeeeha cowboy!
 
Old April 1st, 2008, 08:32 AM
Authorized User
 
Join Date: May 2005
Posts: 28
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to shaileshk Send a message via Yahoo to shaileshk
Default

http://www.codegroups.com/blog/index...-the-starting/

try this link

I hope this is help to you.

shailesh kavathiya





Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I keep leading and trailing whitespaces? 2BOrNot2B XSLT 2 October 31st, 2008 11:35 AM
Removing leading zeros from a string value vikkiefd XSLT 2 June 12th, 2008 03:30 AM
Remove Leading and Trailing Zeros lene_wee SQL Server 2000 3 April 1st, 2008 08:23 AM
Remove the trailing whitespace ksskumar XSLT 1 October 13th, 2006 06:23 AM
Audit trailing using SQL server log file madhukp SQL Server 2000 1 April 4th, 2006 01:11 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.