x

Remove Decimal

Dears,

Can you please help on REMOVING LAST CHAR from 4 decimal value, WITHOUT ROUNDING decimal value?
more ▼

asked May 21, 2012 at 08:47 AM in Default

Gogolo gravatar image

Gogolo
323 23 26 27

can you give us a sample of the source data and how you want the target data to look like?
May 21, 2012 at 09:03 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

It would be easier to give you the right answer if we had an example to work with, but the truncate function of the round function may give you what you need:

declare @YourDecimal decimal(8,4)
set @YourDecimal = 1.1234

select cast(round(@YourDecimal,3,1) as decimal(8,3))
The third argument of the ROUND function allows you to specify that the rounding should be done by truncation rather than 'normal' rounding, so you can use this to truncate the value to a decimal with 3 decimal places, and then re-cast that, otherwise the ROUND function returns the same scale as the value passed in.
more ▼

answered May 21, 2012 at 09:56 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

Kev, Great solution..just without last digit, while I have all records with 4 decimals, I got a simple solution,

LEFT(@Field,CHARINDEX('.',@Field)+3)

Thank you very much for your effort.
May 21, 2012 at 10:27 AM Gogolo
(comments are locked)
10|1200 characters needed characters left

The method depends on the source data type. Some will round others will truncate. You seems to be looking for a method that truncates.
CAST and CONVERT BOL - http://msdn.microsoft.com/en-us/library/ms187928

-- This is an example where conversion to an INT from a Numeric truncates the values
DECLARE @Values TABLE ( Val DECIMAL(8, 4) )

INSERT  INTO @Values
       ( Val
       )
       SELECT TOP 2000
          ( ABS(CHECKSUM(NEWID())) % 5000.0 + 1 )
          + ( ABS(CHECKSUM(NEWID())) % 2000.0 + 1 ) * .0003 AS [value]
       FROM  [sys].[syscolumns] AS s
          CROSS JOIN [sys].[syscolumns] AS s2

SELECT  val ,
       CONVERT(INT, val) AS [converted]
FROM @Values AS v
more ▼

answered May 21, 2012 at 09:50 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x587
x6

asked: May 21, 2012 at 08:47 AM

Seen: 1796 times

Last Updated: May 21, 2012 at 10:35 AM