Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using a variable to limit the decimal places

Posted on 2016-10-18
10
Medium Priority
?
84 Views
Last Modified: 2016-10-18
I have a numeric data field (Numeric(18,6)) This is concatenated into a string with some other text values.

Trouble is not all clients want 6 decimal places after the int. I have tried Round() but his retains the 6 decimal places. Converting to a varchar does the job, but how to I make it a variable from probably 2 to 6 in length.

I have a setting table where this value is stored.

Andy
0
Comment
Question by:Andy Green
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41847866
to make your system more robust, just try to store the values in 6 decimal points, and when you want to output it, read the setting table and format the value accordingly.
0
 
LVL 3

Author Comment

by:Andy Green
ID: 41847876
Hi Ryan - this is what I'm doing the number is stored to 6dp, and I'm asking how to display a lower dp based on my setting table.

Andy
0
 
LVL 35

Expert Comment

by:ste5an
ID: 41847877
Just do it in the front-end application/consumer.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 3

Author Comment

by:Andy Green
ID: 41847887
Hi ste5an - not able to do in front end easily.

This is someone else's code and a bit convoluted. The string is concatenated in SQL, and then parsed through an XML interpreter for the final display. Would take longer than I have to pick apart the XML and apply a transform.

For now I can change the stored proc to use Varchar(4) but not ideal.

Andy
0
 
LVL 53

Assisted Solution

by:Ryan Chong
Ryan Chong earned 400 total points
ID: 41847895
this is what I'm doing the number is stored to 6dp, and I'm asking how to display a lower dp based on my setting table
you can always do that in front-end as what already mentioned by ste5an, or you can create a stored procedure, in which it reads the setting table and then customize the values if necessary.

try customize:
CREATE PROCEDURE  [dbo].[getRecords]
AS
BEGIN
	 SET NOCOUNT ON;  	
	
	 DECLARE @SQL varchar(500
	 DECLARE @DecimalPts VARCHAR(20)
	 
	 SET @DecimalPts = (SELECT Value FROM Setting WHERE SettingField = 'DecimalPoints')
	 
     SET @SQL = 'SELECT *, COnvert(decimal(18, '+@DecimalPts+'), MyField1) FROM Table1'
	
	 EXEC(@SQL);
END

Open in new window

0
 
LVL 35

Expert Comment

by:ste5an
ID: 41847906
There is a nice term called "technical debt".. imho you have to deal with it.

This is someone else's code and a bit convoluted. The string is concatenated in SQL, and then parsed through an XML interpreter for the final display. Would take longer than I have to pick apart the XML and apply a transform.

Why concatenating it first, when you later have to pick it apart? Sounds - well - pretty unusual.
0
 
LVL 14

Accepted Solution

by:
Nakul Vachhrajani earned 1600 total points
ID: 41847973
Round does, and should work (see example below). I guess, you may not be passing the precision parameter to the ROUND() function which is why it may not be working in your case.

Of course, in order to concatenate it, you will need to cast it back to VARCHAR/NVARCHAR.

USE tempdb;
GO
DECLARE @valueOfPi DECIMAL(18,6);
DECLARE @requiredDecimals INT = 2;

SELECT @valueOfPi = PI();

SELECT @valueOfPi AS ActualValue, 
       ROUND(@valueOfPi, @requiredDecimals) AS RoundedValue, 
       CAST(ROUND(@valueOfPi, @requiredDecimals) AS VARCHAR(20)) AS StringRepresentation;

/* RESULTS
ActualValue  RoundedValue  StringRepresentation
------------ ------------- --------------------
3.141593     3.140000      3.140000
*/

Open in new window

0
 
LVL 3

Author Comment

by:Andy Green
ID: 41847991
Thanks Nakul - but as you can see from your example Round() still displays 6 decimals padded with zeros, I'd be looking for a result of 3.14.

ste5an - the SQL is quite long so would take some working out to use your approach, but does seem to be the only way.

I know technical debt, and we have a ton of it, from varchars with no specified lengths, unnecessary cursors, even no Using statements in the front end classes to auto clean up.

Its a bit of a mess, with urgent changes required and no idea what the code is doing - ho hum...
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41848189
You'll need to truncate the converted value so it will get rid of the 0's. Check the LEFT function below:
DECLARE @MyValue Numeric(18,6)=RAND()
DECLARE @MyString VARCHAR(100)='The number is:'
DECLARE @DecPlaces INT = 5

DECLARE @MyCastValue VARCHAR(20) = CAST(ROUND(@MyValue, @DecPlaces) AS VARCHAR)

SET @MyCastValue = LEFT(@MyCastValue, CHARINDEX('.', @MyCastValue)+@DecPlaces)
SET @MyString = @MyString + @MyCastValue

PRINT @MyCastValue
PRINT @MyValue
PRINT @MyString

Open in new window

0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 41848285
Thanks Guys. All sorted now.

A
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question