Remove Decimal


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

avatar image

323 24 26 31

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 voted first

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

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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


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

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: May 21, 2012 at 08:47 AM

Seen: 2354 times

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

Copyright 2018 Redgate Software. Privacy Policy