Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Rounding off the value Reply to Topic  Printer Friendly
Author  Topic

bpuccha
Starting Member

34 Posts

 Posted - 01/17/2013 :  15:37:58 I have to round off the value to 2 decimal places, I did that through round function as followsselect round(1023.245,2,0)----1023.25(But I want it as 1023.24,it should round down,instead of up)select round(1023.246,2,0)----1023.25But I dont want to round the value if the 3rd digit after the decimal is 5.So the first value should come as 1023.24We can use round(1023.245,2,1), but it always trucates the value.I need to round down only it has 5. Edited by - bpuccha on 01/17/2013 15:49:54

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

 Posted - 01/17/2013 :  16:12:57 `Select case when right(cast(1023.242 as Varchar(20)),1) > 5 then round(1023.242,2) else round(1023.242,2,1) end`

bpuccha
Starting Member

34 Posts

 Posted - 01/17/2013 :  16:18:12 I can do that, but in my stored procedure i am caluculating 10 diff values based on that amt(1023.245), the calculations are pretty complex like below.So evertime i have to use case statement,Is there any other way???(@p_due_amt/2)+(@p_due_amt - (round((@p_due_amt/2),2,0)*2))

James K
Flowing Fount of Yak Knowledge

3873 Posts

 Posted - 01/17/2013 :  17:44:41 If you include a subtract of 0.001 on all occurrences where you want this rule to be applied that should do it - for example:`SELECT ROUND(1023.245-0.001,2,0)`I prefer casting to decimal of the appropriate scale rather than rounding because of cases like the following where the results can overflow```SELECT ROUND(999.996,2,0) Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type numeric. SELECT CAST(999.996 AS DECIMAL(18,2)) ```

bpuccha
Starting Member

34 Posts

 Posted - 01/18/2013 :  09:55:23 Thanks for the reply. But 1023.245 is not a constant value, it varies depends on the input parameters.So i can't substract 0.001 from that value everytime.

James K
Flowing Fount of Yak Knowledge

3873 Posts

 Posted - 01/18/2013 :  13:16:19 I was only showing any example. It works the same way with a constant, variable, or column - i.e, subtract 0.001 and round to 2 decmials, and you will be effectively rounding down any fractional part that has a 5 in the third decimal place. All other numbers would behave the same way as before.

bpuccha
Starting Member

34 Posts

 Posted - 01/21/2013 :  12:22:26 I tested that,,It worked..Thank u soo much for the reply Edited by - bpuccha on 01/21/2013 12:25:29

James K
Flowing Fount of Yak Knowledge

3873 Posts

 Posted - 01/21/2013 :  16:00:19 You are very welcome - glad it helped.
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC